pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate trunc in UTC
10+ messages / 4 participants
[nested] [flat]
* Date trunc in UTC
@ 2002-11-20 09:44 Thrasher <thrasher@fibers.upc.es>
0 siblings, 1 reply; 10+ messages in thread
From: Thrasher @ 2002-11-20 09:44 UTC (permalink / raw)
To: pgsql-sql
Hi
I do not know if it's an error, but in this query
=# select date_trunc ('month', now ());
date_trunc
------------------------
2002-11-01 00:00:00+01
(1 row)
I've got the truncated date dependant to my timezone.
Instead, I would like to have as a result
2002-11-01 01:00:00+01
which is correct, but I cannot set the whole server to UTC. Any way to
get this ?
Thanks
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-20 10:24 Richard Huxton <dev@archonet.com>
parent: Thrasher <thrasher@fibers.upc.es>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2002-11-20 10:24 UTC (permalink / raw)
To: Thrasher <thrasher@fibers.upc.es>; pgsql-sql
On Wednesday 20 Nov 2002 9:44 am, Thrasher wrote:
> Hi
>
> I do not know if it's an error, but in this query
>
> =# select date_trunc ('month', now ());
> date_trunc
> ------------------------
> 2002-11-01 00:00:00+01
> (1 row)
>
> I've got the truncated date dependant to my timezone.
>
> Instead, I would like to have as a result
>
> 2002-11-01 01:00:00+01
>
> which is correct, but I cannot set the whole server to UTC. Any way to
> get this ?
Perhaps SET TIME ZONE is what you want. See the manual section on date/time
types for details.
--
Richard Huxton
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-20 10:37 Thrasher <thrasher@fibers.upc.es>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Thrasher @ 2002-11-20 10:37 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
No I cannot use SET TIME ZONE.
SET TIME ZONE will be set by any client backend. But what I want to get
is that DATE_TRUNC('month', <now in timezone +1>) = DATE_TRUNC('month',
<now in timezone -7>).
Richard Huxton wrote:
> On Wednesday 20 Nov 2002 9:44 am, Thrasher wrote:
>
>>Hi
>>
>>I do not know if it's an error, but in this query
>>
>>=# select date_trunc ('month', now ());
>> date_trunc
>>------------------------
>> 2002-11-01 00:00:00+01
>>(1 row)
>>
>>I've got the truncated date dependant to my timezone.
>>
>>Instead, I would like to have as a result
>>
>> 2002-11-01 01:00:00+01
>>
>>which is correct, but I cannot set the whole server to UTC. Any way to
>>get this ?
>
>
> Perhaps SET TIME ZONE is what you want. See the manual section on date/time
> types for details.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-20 13:06 Richard Huxton <dev@archonet.com>
parent: Thrasher <thrasher@fibers.upc.es>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2002-11-20 13:06 UTC (permalink / raw)
To: Thrasher <thrasher@fibers.upc.es>; +Cc: pgsql-sql
On Wednesday 20 Nov 2002 10:37 am, Thrasher wrote:
> No I cannot use SET TIME ZONE.
>
> SET TIME ZONE will be set by any client backend. But what I want to get
> is that DATE_TRUNC('month', <now in timezone +1>) = DATE_TRUNC('month',
> <now in timezone -7>).
Sorry, I've obviously misunderstood. Are you just looking to discard the
timezone so they look the same?
select date_trunc('month', CAST(CURRENT_TIMESTAMP AS timestamp without time
zone));
date_trunc
---------------------
2002-11-01 00:00:00
I'd have thought that would give you some problems around local/utc midnight
on the first of the month.
Or is it that you want to know what time it was in UTC zone at the start of
the month local time?
If I'm still being a bit slow (quite likely) can you explain what you're using
this for?
> >>=# select date_trunc ('month', now ());
> >> date_trunc
> >>------------------------
> >> 2002-11-01 00:00:00+01
> >>Instead, I would like to have as a result
> >>
> >> 2002-11-01 01:00:00+01
> >>
> >>which is correct, but I cannot set the whole server to UTC. Any way to
> >>get this ?
--
Richard Huxton
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-20 15:40 Juan Fernandez <jfernandez@electronic-group.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Juan Fernandez @ 2002-11-20 15:40 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Hi Richard
Ok, I'll do my best to explain clearer ;)
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.
I have done a plpgsql procedure 'increase_counter' that increases the
counter 'visits = visits + 1' every time it gets called. But, I have to
check if I went into the next month, so basically I do
UPDATE traffic SET visits = visits + 1 WHERE service = 'chat' AND
month = DATE_TRUNC (''month'', ''now''::timestamp);
If there was no row updated, then I create the new entry as
INSERT INTO traffic VALUES
('chat', DATE_TRUNC (''month'', ''now''::timestamp), 1);
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.
2002-11-01 00:00:00+00 (UTC)
So, if I'm working in the CET timezone, what I would like to see is
2002-11-01 01:00:00+01 (CET)
Or, if I'm working with another time zone,
2002-10-31 16:00:00-08 (dunno timezone name)
TABLE traffic
+---------+------------------------+--------+
| service | month | visits |
+---------+------------------------+--------+
| chat | 2002-11-01 01:00:00+01 | 37002 |
| video | 2002-11-01 01:00:00+01 | 186354 |
| chat | 2002-10-01 01:00:00+01 | 41246 |
| video | 2002-10-01 01:00:00+01 | 86235 |
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.
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.
I hope I explained well ;)
Thanks for everything
Richard Huxton wrote:
> On Wednesday 20 Nov 2002 10:37 am, Thrasher wrote:
>
>>No I cannot use SET TIME ZONE.
>>
>>SET TIME ZONE will be set by any client backend. But what I want to get
>>is that DATE_TRUNC('month', <now in timezone +1>) = DATE_TRUNC('month',
>><now in timezone -7>).
>
>
> Sorry, I've obviously misunderstood. Are you just looking to discard the
> timezone so they look the same?
>
> select date_trunc('month', CAST(CURRENT_TIMESTAMP AS timestamp without time
> zone));
> date_trunc
> ---------------------
> 2002-11-01 00:00:00
>
> I'd have thought that would give you some problems around local/utc midnight
> on the first of the month.
>
> Or is it that you want to know what time it was in UTC zone at the start of
> the month local time?
>
> If I'm still being a bit slow (quite likely) can you explain what you're using
> this for?
>
>
>>>>=# select date_trunc ('month', now ());
>>>> date_trunc
>>>>------------------------
>>>> 2002-11-01 00:00:00+01
>>>
>
>>>>Instead, I would like to have as a result
>>>>
>>>> 2002-11-01 01:00:00+01
>>>>
>>>>which is correct, but I cannot set the whole server to UTC. Any way to
>>>>get this ?
>>>
>
--
Juan A. FERNANDEZ-REBOLLOS - jfernandez@electronic-group.com
Mobile Dept.
_________________________________________________________
ELECTRONIC GROUP INTERACTIVE - www.electronic-group.com
World Trade Center, Moll de BARCELONA
Edificio Norte 4 Planta
08039 BARCELONA SPAIN
Tel : +34 93600 23 23 Fax : +34 93600 23 10
_________________________________________________________
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-21 10:15 Richard Huxton <dev@archonet.com>
parent: Juan Fernandez <jfernandez@electronic-group.com>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2002-11-21 10:15 UTC (permalink / raw)
To: thrasher@fibers.upc.es; +Cc: 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.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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-21 15:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2002-11-21 15:26 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: thrasher@fibers.upc.es, pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Richard Huxton <dev@archonet.com> writes:
> 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.
In 7.3 you can use current_setting() and set_config() to access SHOW/SET
functionality. However, I agree with your suggestion of AT TIME ZONE
to rotate a timestamp into a target timezone, rather than mucking with
the TimeZone setting.
BTW, Thomas: is AT TIME ZONE supposed to accept
timestamp-without-timezone input? If so, what's it supposed to do with
it? The current behavior seems unintuitive to say the least:
regression=# select now();
now
-------------------------------
2002-11-21 10:19:14.591001-05
(1 row)
regression=# select now() at time zone 'UTC';
timezone
----------------------------
2002-11-21 15:19:18.588279
(1 row)
regression=# select localtimestamp;
timestamp
----------------------------
2002-11-21 10:19:22.629865
(1 row)
regression=# select localtimestamp at time zone 'UTC';
timezone
-------------------------------
2002-11-21 05:19:26.178861-05
(1 row)
It seems to me that the last case should give either an error or
2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time
zone is in my TimeZone zone). In any case, surely the result should
be of type timestamp WITHOUT time zone?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-22 09:11 Thrasher <thrasher@fibers.upc.es>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Thrasher @ 2002-11-22 09:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; thrasher@fibers.upc.es, pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
The biggest point that I see is that it would be nice to have some kind
of function that works with UTC values, regarding of which timezone the
user has set. Let's say, something like
SELECT UTC_DATE_TRUNC ('month', NOW ());
utc_date_trunc
------------------------
2002-11-01 01:00:00+01
because if you work with international applications, the beggining of
the month in Spain should be the same as in Australia. But everyone will
see it in its own timezone.
I think that it would be also interesting to have the UTCeed versions of
EXTRACT and AGE.
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>
>>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.
>
>
> In 7.3 you can use current_setting() and set_config() to access SHOW/SET
> functionality. However, I agree with your suggestion of AT TIME ZONE
> to rotate a timestamp into a target timezone, rather than mucking with
> the TimeZone setting.
>
>
> BTW, Thomas: is AT TIME ZONE supposed to accept
> timestamp-without-timezone input? If so, what's it supposed to do with
> it? The current behavior seems unintuitive to say the least:
>
> regression=# select now();
> now
> -------------------------------
> 2002-11-21 10:19:14.591001-05
> (1 row)
>
> regression=# select now() at time zone 'UTC';
> timezone
> ----------------------------
> 2002-11-21 15:19:18.588279
> (1 row)
>
> regression=# select localtimestamp;
> timestamp
> ----------------------------
> 2002-11-21 10:19:22.629865
> (1 row)
>
> regression=# select localtimestamp at time zone 'UTC';
> timezone
> -------------------------------
> 2002-11-21 05:19:26.178861-05
> (1 row)
>
> It seems to me that the last case should give either an error or
> 2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time
> zone is in my TimeZone zone). In any case, surely the result should
> be of type timestamp WITHOUT time zone?
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-11-22 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thrasher <thrasher@fibers.upc.es>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2002-11-22 15:10 UTC (permalink / raw)
To: Thrasher <thrasher@fibers.upc.es>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Thrasher <thrasher@fibers.upc.es> writes:
> The biggest point that I see is that it would be nice to have some kind
> of function that works with UTC values, regarding of which timezone the
> user has set.
You can do that in 7.3, using the AT TIME ZONE construct. Observe:
regression-# begin;
BEGIN
regression=# select now();
now
-------------------------------
2002-11-22 09:59:48.706508-05 'now' in local time (EST)
(1 row)
regression=# select now() at time zone 'UTC';
timezone
----------------------------
2002-11-22 14:59:48.706508 'now' in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC');
date_trunc
---------------------
2002-11-01 00:00:00 month start in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC') at time zone 'UTC';
timezone
------------------------
2002-10-31 19:00:00-05 month start in local time
(1 row)
regression=# commit;
This is a tad bulky maybe, but it'd be easy to wrap up in a user-defined
function.
AT TIME ZONE was less functional, and very poorly documented, in 7.2.
The 7.3 version is described at
http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date trunc in UTC
@ 2002-12-03 09:30 Thrasher <thrasher@fibers.upc.es>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Thrasher @ 2002-12-03 09:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Hi all
Finally, I am using a plpgsql procedure that accomplish that in
PostgreSQL 7.2.1. The code follows:
CREATE OR REPLACE FUNCTION utc_date_trunc (TEXT, TIMESTAMP) RETURNS
TIMESTAMP AS '
DECLARE
utcts TIMESTAMP WITHOUT TIME ZONE;
utcdt TIMESTAMP WITHOUT TIME ZONE;
BEGIN
--> First get the indicated timestamp at UTC <--
utcts := $2 AT TIME ZONE ''UTC'';
--> Get the date trunc <--
utcdt := DATE_TRUNC ($1, utcts);
--> Return the result <--
RETURN (utcdt at time zone ''UTC'');
END
' LANGUAGE 'plpgsql';
I guess that it can be adapted for other time functions with ease, but I
do not need them yet.
Thanks to everybody involved,
Thrasher
Tom Lane wrote:
> Thrasher <thrasher@fibers.upc.es> writes:
>
>>The biggest point that I see is that it would be nice to have some kind
>>of function that works with UTC values, regarding of which timezone the
>>user has set.
>
>
> You can do that in 7.3, using the AT TIME ZONE construct. Observe:
>
> regression-# begin;
> BEGIN
> regression=# select now();
> now
> -------------------------------
> 2002-11-22 09:59:48.706508-05 'now' in local time (EST)
> (1 row)
>
> regression=# select now() at time zone 'UTC';
> timezone
> ----------------------------
> 2002-11-22 14:59:48.706508 'now' in UTC
> (1 row)
>
> regression=# select date_trunc('month', now() at time zone 'UTC');
> date_trunc
> ---------------------
> 2002-11-01 00:00:00 month start in UTC
> (1 row)
>
> regression=# select date_trunc('month', now() at time zone 'UTC') at time zone 'UTC';
> timezone
> ------------------------
> 2002-10-31 19:00:00-05 month start in local time
> (1 row)
>
> regression=# commit;
>
> This is a tad bulky maybe, but it'd be easy to wrap up in a user-defined
> function.
>
> AT TIME ZONE was less functional, and very poorly documented, in 7.2.
> The 7.3 version is described at
> http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-12-03 09:30 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-20 09:44 Date trunc in UTC Thrasher <thrasher@fibers.upc.es>
2002-11-20 10:24 ` Richard Huxton <dev@archonet.com>
2002-11-20 10:37 ` Thrasher <thrasher@fibers.upc.es>
2002-11-20 13:06 ` Richard Huxton <dev@archonet.com>
2002-11-20 15:40 ` Juan Fernandez <jfernandez@electronic-group.com>
2002-11-21 10:15 ` Richard Huxton <dev@archonet.com>
2002-11-21 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-22 09:11 ` Thrasher <thrasher@fibers.upc.es>
2002-11-22 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-12-03 09:30 ` Thrasher <thrasher@fibers.upc.es>
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