Re: Date trunc in UTC - Mailing list pgsql-sql

On Wednesday 20 Nov 2002 3:40 pm, Juan Fernandez wrote:
> Hi Richard
>
> Ok, I'll do my best to explain clearer ;)

I'll do my best to be of some use ;-)

> I have to make some monthly reports about some service requests
> activity. So, I'm keeping in a table the monthly traffic.
>
> TABLE traffic
> +---------+------------------------+--------+
>
> | service | month                  | visits |
>
> +---------+------------------------+--------+
>
> | chat    | 2002-11-01 00:00:00+01 |  37002 |
> | video   | 2002-11-01 00:00:00+01 | 186354 |
> | chat    | 2002-10-01 00:00:00+01 |  41246 |
> | video   | 2002-10-01 00:00:00+01 |  86235 |
>
> So, when I have a new visit on any service, I increase the counter for
> that month. The problems are:
>
> - As you see, the month includes timezone information (+01), which
> corresponds to the CET beggining of the month.
>
> - Whenever a new month starts, I have to create a new entry in the table.
[snip]
> So, as I can see in the traffic table, the DATE_TRUNC is, in fact,
> equivalent to
>
>     2002-11-01 00:00:00+01 (CET) == 2002-09-30 23:00:00+00 (UTC)
>
> If we think that I will work in an international environment, I would
> rather to have in the table as the result of the DATE_TRUNC the right
> UTC value, so, the right begginning of the month in UTC.
[snip]
> In fact, DATE_TRUNC is returning the beggining of the month FOR THE
> WORKING TIME ZONE, but I need to know, in my timezone, what is the
> begginning of the UTC month.

Ah! now I understand. Is this the sort of thing you're after?

=> SELECT now() AT TIME ZONE 'PST' AS allsame
UNION SELECT now() AT TIME ZONE 'UTC'
UNION SELECT now() AT TIME ZONE 'CCT';         allsame
----------------------------2002-11-21 02:00:17.6150672002-11-21 10:00:17.6150672002-11-21 18:00:17.615067
(3 rows)

Above was run at about 10am local time (I'm in London). Note the lack of
timezone on the end.

> Another more problem is that if I set the time zone in the session, I'm
> not able to recover to its previous state. In plpgsql,
>
> client preferences -> SET TIME ZONE 'PST8PDT';
>
>     ... calling to my wrapper function
>
> CREATE FUNCTION date_trunc_utc (TEXT, TIMESTAMP) RETURN TIMESTAMP AS '
>     DECLARE
>         st_month TIMESTAMP;
>     BEGIN
>         SET TIME ZONE ''UTC'';
>         st_month = DATE_TRUNC ($1, $2);
>         RESET TIME ZONE;
>     END
> ' LANGUAGE 'plpgsql';
>
> -> SHOW TIME ZONE
> NOTICE:  Time zone is 'CET'
>
>
> so basically, I cannot change to UTC because I'm not able no more to
> recover to the client timezone preferences.

Hmm - good point. You can revert to the client default but not to the previous
value. I don't know of any way to read these SET values either - a quick poke
through pg_proc didn't show anything likely.

--  Richard Huxton