Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: thrasher@fibers.upc.es
Subject: Re: Date trunc in UTC
Date: Thu, 21 Nov 2002 10:15:41 +0000
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <3DDB5991.9060706@fibers.upc.es>
	<200211201306.47345.dev@archonet.com>
	<3DDBAD02.3090406@electronic-group.com>
In-Reply-To: <3DDBAD02.3090406@electronic-group.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211211015.42013.dev@archonet.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) =3D=3D 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?

=3D> SELECT now() AT TIME ZONE 'PST' AS allsame=20
UNION SELECT now() AT TIME ZONE 'UTC'=20
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=20
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 =3D 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 previ=
ous=20
value. I don't know of any way to read these SET values either - a quick po=
ke=20
through pg_proc didn't show anything likely.

--=20
  Richard Huxton