Thread: Date trunc in UTC

Date trunc in UTC

From

Thrasher

Date:

20 November 2002, 07:45:23

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

Re: Date trunc in UTC

From

Richard Huxton

Date:

20 November 2002, 08:30:07

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

Re: Date trunc in UTC

From

Thrasher

Date:

20 November 2002, 08:38:01

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.
>

Re: Date trunc in UTC

From

Richard Huxton

Date:

20 November 2002, 11:15:08

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

Re: Date trunc in UTC

From

Richard Huxton

Date:

21 November 2002, 08:30:09

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

Re: Date trunc in UTC

From

Tom Lane

Date:

21 November 2002, 13:27:08

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

Re: Date trunc in UTC

From

Thrasher

Date:

22 November 2002, 07:11:47

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
> 
>

Re: Date trunc in UTC

From

Tom Lane

Date:

22 November 2002, 13:12:51

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

Re: Date trunc in UTC

From

Juan Fernandez

Date:

26 November 2002, 12:09:24

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' ANDmonth = 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
_________________________________________________________

Re: Date trunc in UTC

From

Thrasher

Date:

03 December 2002, 07:30:42

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    -->
Firstget 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
> 
>