From: Richard Huxton <dev@archonet.com>
To: thrasher@fibers.upc.es
Cc: pgsql-sql@postgresql.org
Subject: Re: Date trunc in UTC
Date: Thu, 21 Nov 2002 10:15:41 +0000
Message-ID: <200211211015.42013.dev@archonet.com> (raw)
In-Reply-To: <3DDBAD02.3090406@electronic-group.com>
References: <3DDB5991.9060706@fibers.upc.es>
<200211201306.47345.dev@archonet.com>
<3DDBAD02.3090406@electronic-group.com>
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.615067
2002-11-21 10:00:17.615067
2002-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, thrasher@fibers.upc.es
Subject: Re: Date trunc in UTC
In-Reply-To: <200211211015.42013.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox