Skip site navigation (1) Skip section navigation (2)

Re: Date trunc in UTC

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thrasher <thrasher(at)fibers(dot)upc(dot)es>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-sql(at)postgresql(dot)org,Thomas Lockhart <lockhart(at)fourpalms(dot)org>
Subject: Re: Date trunc in UTC
Date: 2002-11-22 15:10:42
Message-ID: 23923.1037977842@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Thrasher <thrasher(at)fibers(dot)upc(dot)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

In response to

Responses

pgsql-sql by date

Next:From: Jean-Luc LachanceDate: 2002-11-22 16:26:25
Subject: Re: calculating interval
Previous:From: Dan LangilleDate: 2002-11-22 14:36:15
Subject: Re: calculating interval

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group