Thread: timezone() with timeofday() converts the wrong direction?
These attempts were run at 4/21/2005 13:15:00 -07.
UTC: 4/21/2005 20:15:00
SELECT timeofday()::timestamp
04/21/2005 13:15:00
SELECT timezone('UTC', now())
04/21/2005 20:15:00 PM
SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
04/21/2005 06:15:00 <- What the heck is this?!
Why does the conversion to UTC using timeofday() go wrong? It seems like the
conversion went 7 hours in the wrong direction. Did I forget a parameter or
switch somewhere?
Thanks,
Steve
On Thu, 2005-04-21 at 15:35, Steve - DND wrote:
> These attempts were run at 4/21/2005 13:15:00 -07.
> UTC: 4/21/2005 20:15:00
>
> SELECT timeofday()::timestamp
> 04/21/2005 13:15:00
>
> SELECT timezone('UTC', now())
> 04/21/2005 20:15:00 PM
>
> SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
> 04/21/2005 06:15:00 <- What the heck is this?!
>
>
> Why does the conversion to UTC using timeofday() go wrong? It seems like the
> conversion went 7 hours in the wrong direction. Did I forget a parameter or
> switch somewhere?
What version of PostgreSQL are you running? I seem to remember a few
versions ago the offset having the wrong sign.
> > What version of PostgreSQL are you running? I seem to remember a few > versions ago the offset having the wrong sign. I'm running 8.0.2 Win32. Steve
On Thu, Apr 21, 2005 at 01:35:16PM -0700, Steve - DND wrote:
> These attempts were run at 4/21/2005 13:15:00 -07.
> UTC: 4/21/2005 20:15:00
>
> SELECT timeofday()::timestamp
> 04/21/2005 13:15:00
>
> SELECT timezone('UTC', now())
> 04/21/2005 20:15:00 PM
>
> SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
> 04/21/2005 06:15:00 <- What the heck is this?!
According to the "Date/Time Functions and Operators" documentation,
timezone(zone, timestamp) is equivalent to "timestamp AT TIME ZONE
zone", and "timestamp without time zone AT TIME ZONE zone" means
"Convert local time in given time zone to UTC" and has a return
type of "timestamp with time zone". It therefore seems to me that
you're converting timeofday() from UTC to UTC and that the output
is converted for display to your local time zone, which you then
strip off. See the example in the documentation:
Examples (supposing that the local time zone is PST8PDT):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
The first example takes a zone-less time stamp and interprets it as
MST time (UTC-7) to produce a UTC time stamp, which is then rotated to
PST (UTC-8) for display. The second example takes a time stamp
specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
We could rewrite the example using your values as follows:
SELECT TIMESTAMP '2005-04-21 13:15:00' AT TIME ZONE 'UTC';
Result: 2005-04-21 06:15:00-07
The first example takes a zone-less time stamp and interprets it as
UTC time to produce a UTC time stamp, which is then rotated to PDT
(UTC-7) for display.
Perhaps this is what you want:
SELECT timezone('UTC', timeofday()::timestamptz);
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
>
> Perhaps this is what you want:
>
> SELECT timezone('UTC', timeofday()::timestamptz);
That did it. Strangely, I thought I had tried that already, but I must not
have. My next question would be if I did:
SELECT timezone('UTC', timeofday()::timestamptz):timestamptz;
Why do I get the timezone value as being -07(my local offset), instead
of -00?
Thanks,
Steve
On Thu, Apr 21, 2005 at 04:11:57PM -0700, Steve - DND wrote:
> >
> > Perhaps this is what you want:
> >
> > SELECT timezone('UTC', timeofday()::timestamptz);
>
> That did it. Strangely, I thought I had tried that already, but I must not
> have. My next question would be if I did:
>
> SELECT timezone('UTC', timeofday()::timestamptz):timestamptz;
>
> Why do I get the timezone value as being -07(my local offset), instead
> of -00?
Again looking at the documentation, we see that "timestamp with
time zone AT TIME ZONE zone" means "Convert UTC to local time in
given time zone" and has a return type of "timestamp without time
zone". So if we run the above command without the final cast
around 16:25 PDT / 23:25 UTC, we get this:
SELECT timezone('UTC', timeofday()::timestamptz);
timezone
----------------------------
2005-04-21 23:25:12.868212
(1 row)
This result is a "timestamp without time zone", so there's no
indication that it's UTC or PDT or anything else. Since it has no
time zone, casting it to timestamptz puts it in your local time
zone:
SELECT '2005-04-21 23:25:12.868212'::timestamptz;
timestamptz
-------------------------------
2005-04-21 23:25:12.868212-07
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Steve - DND wrote:
> >
> > What version of PostgreSQL are you running? I seem to remember a few
> > versions ago the offset having the wrong sign.
>
> I'm running 8.0.2 Win32.
BSD Unix looks strange too:
test=> select current_timestamp;
timestamptz
-------------------------------
2005-04-21 19:45:55.553635-04
(1 row)
test=> SELECT timezone('UTC', current_timestamp::timestamp);
timezone
-------------------------------
2005-04-21 15:46:12.740201-04
(1 row)
test=> SELECT timezone('UTC', current_timestamp::timestamp)::timestamp
without time zone;
timezone
----------------------------
2005-04-21 15:46:14.333257
(1 row)
But these look fine:
test=> SELECT timezone('UTC', current_timestamp);
timezone
---------------------------
2005-04-21 23:48:18.60604
(1 row)
test=> SELECT current_timestamp::timestamp;
timestamp
----------------------------
2005-04-21 19:51:25.867765
(1 row)
test=> SELECT current_timestamp::timestamp with time zone;
timestamptz
-------------------------------
2005-04-21 19:51:30.178186-04
(1 row)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> Again looking at the documentation, we see that "timestamp with
> time zone AT TIME ZONE zone" means "Convert UTC to local time in
> given time zone" and has a return type of "timestamp without time
> zone". So if we run the above command without the final cast
> around 16:25 PDT / 23:25 UTC, we get this:
>
> SELECT timezone('UTC', timeofday()::timestamptz);
> timezone
> ----------------------------
> 2005-04-21 23:25:12.868212
> (1 row)
>
> This result is a "timestamp without time zone", so there's no
> indication that it's UTC or PDT or anything else. Since it has no
> time zone, casting it to timestamptz puts it in your local time
> zone:
>
> SELECT '2005-04-21 23:25:12.868212'::timestamptz;
> timestamptz
> -------------------------------
> 2005-04-21 23:25:12.868212-07
> (1 row)
Okay, I understand what you're saying now, but then is a time without a
timezone implicitly assumed to be UTC? Is there a way to explicitly make the
timezone on the stamp be UTC, if the prior is not the case?
Steve
On Thu, Apr 21, 2005 at 05:56:41PM -0700, Steve - DND wrote: > > Okay, I understand what you're saying now, but then is a time without a > timezone implicitly assumed to be UTC? Is there a way to explicitly make the > timezone on the stamp be UTC, if the prior is not the case? See "Date/Time Types" in the "Data Types" chapter of the documentation: http://www.postgresql.org/docs/8.0/interactive/datatype-datetime.html "Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different zone reference can be specified for the conversion using AT TIME ZONE." "All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client." As far as I know, it's not possible to get output like the following from the same query if the data type is timestamp with time zone: 2005-04-21 15:00:00-07 2005-04-21 22:00:00+00 However, I suppose you could muck around with the TimeZone configuration variable and cast the timestamps to text: CREATE FUNCTION tzconvert(timestamp with time zone, text) RETURNS text AS ' DECLARE ts ALIAS FOR $1; tz ALIAS FOR $2; tmptz text; retval text; BEGIN tmptz := current_setting(''TimeZone''); PERFORM set_config(''TimeZone'', tz, TRUE); retval := ts; PERFORM set_config(''TimeZone'', tmptz, TRUE); RETURN retval; END; ' LANGUAGE plpgsql VOLATILE STRICT; SELECT tzconvert('2005-04-21 15:00:00-07', 'PST8PDT') AS pacific, tzconvert('2005-04-21 15:00:00-07', 'UTC') AS utc; pacific | utc ------------------------+------------------------ 2005-04-21 15:00:00-07 | 2005-04-21 22:00:00+00 (1 row) Maybe somebody knows of an easier way to do that. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
> As far as I know, it's not possible to get output like the following > from the same query if the data type is timestamp with time zone: > > 2005-04-21 15:00:00-07 > 2005-04-21 22:00:00+00 Doesn't "at time zone" do what you need ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, Apr 24, 2005 at 05:21:41PM +0200, Karsten Hilbert wrote: > > > > As far as I know, it's not possible to get output like the following > > from the same query if the data type is timestamp with time zone: > > > > 2005-04-21 15:00:00-07 > > 2005-04-21 22:00:00+00 > > Doesn't "at time zone" do what you need ? Not as far as I can tell, because if the result is timestamp with time zone then it's rotated to the local time zone for display. If you can post a counterexample then I'd be happy to stand corrected. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sun, Apr 24, 2005 at 10:49:13AM -0600, Michael Fuhr wrote:
> > > As far as I know, it's not possible to get output like the following
> > > from the same query if the data type is timestamp with time zone:
> > >
> > > 2005-04-21 15:00:00-07
> > > 2005-04-21 22:00:00+00
> >
> > Doesn't "at time zone" do what you need ?
>
> Not as far as I can tell, because if the result is timestamp with
> time zone then it's rotated to the local time zone for display. If
> you can post a counterexample then I'd be happy to stand corrected.
Isn't the following what you want?
************************************************************
»Script« wurde gestartet: Sun Apr 24 19:05:06 2005
ncq@hermes:~> psql -d gnumed -U any-doc
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
gnumed=> select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)
gnumed=> create teable test (f timestamp with time zone);
CREATE
gnumed=> set time zone '+2:00';
SET VARIABLE
gnumed=> show time zone;
NOTICE: Time zone is +2:00
SHOW VARIABLE
gnumed=> insert into test (f) values (now());
INSERT 6462134 1
gnumed=> SELECT * from test;
f
------------------------
2005-04-24 17:06:10+00
(1 row)
gnumed=> select f, f::timestamp at time zone 'MEZ' from test;
f | timezone
------------------------+------------------------
2005-04-24 17:06:10+00 | 2005-04-24 18:06:10+01
(1 row)
gnumed=> \q
ncq@hermes:~> exit
exit
»Script« beendet: Sun Apr 24 19:06:59 2005
************************************************************
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, Apr 24, 2005 at 07:09:44PM +0200, Karsten Hilbert wrote:
> Isn't the following what you want?
...
> gnumed=> select version();
> version
> ---------------------------------------------------------------
> PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
That *definitely* isn't anything I'd want. That version of PostgreSQL
is almost four years old and has serious data loss problems. Aside
from numerous bug fixes, there have been changes in behavior since
then.
> gnumed=> create teable test (f timestamp with time zone);
> CREATE
Does that really work in 7.1.3? Trying to create a "teable" fails
with a syntax error in all the versions I tried (7.2.7, 7.3.9,
7.4.7, 8.0.2, all from CVS). I'll create a "table" instead.
> gnumed=> set time zone '+2:00';
> SET VARIABLE
> gnumed=> show time zone;
> NOTICE: Time zone is +2:00
> SHOW VARIABLE
> gnumed=> insert into test (f) values (now());
> INSERT 6462134 1
> gnumed=> SELECT * from test;
> f
> ------------------------
> 2005-04-24 17:06:10+00
> (1 row)
>
> gnumed=> select f, f::timestamp at time zone 'MEZ' from test;
> f | timezone
> ------------------------+------------------------
> 2005-04-24 17:06:10+00 | 2005-04-24 18:06:10+01
> (1 row)
The above example gives different results depending on the version
of PostgreSQL, and none of the versions I tested show output in
different time zones. The following were all run around 22:25 UTC
or a few minutes later:
7.2.7
create table test (f timestamp with time zone);
CREATE
set time zone '+2:00';
SET VARIABLE
show time zone;
psql:foo.sql:4: NOTICE: Time zone is '+2:00'
SHOW VARIABLE
insert into test (f) values (now());
INSERT 37830 1
SELECT * from test;
f
-------------------------------
2005-04-24 22:25:51.669218+00
(1 row)
select f, f::timestamp at time zone 'MEZ' from test;
f | timezone
-------------------------------+----------------------------
2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
(1 row)
7.3.9
create table test (f timestamp with time zone);
CREATE TABLE
set time zone '+2:00';
SET
show time zone;
TimeZone
----------
+2:00
(1 row)
insert into test (f) values (now());
INSERT 731399 1
SELECT * from test;
f
-------------------------------
2005-04-24 22:26:41.733617+00
(1 row)
select f, f::timestamp at time zone 'MEZ' from test;
f | timezone
-------------------------------+-------------------------------
2005-04-24 22:26:41.733617+00 | 2005-04-24 21:26:41.733617+00
(1 row)
7.4.7
create table test (f timestamp with time zone);
CREATE TABLE
set time zone '+2:00';
SET
show time zone;
TimeZone
----------
+2:00
(1 row)
insert into test (f) values (now());
INSERT 450835 1
SELECT * from test;
f
-------------------------------
2005-04-24 22:27:25.631466+00
(1 row)
select f, f::timestamp at time zone 'MEZ' from test;
f | timezone
-------------------------------+-------------------------------
2005-04-24 22:27:25.631466+00 | 2005-04-24 21:27:25.631466+00
(1 row)
8.0.2
create table test (f timestamp with time zone);
CREATE TABLE
set time zone '+2:00';
psql:foo.sql:3: ERROR: unrecognized time zone name: "+2:00"
set time zone '+2';
SET
show time zone;
TimeZone
----------
02:00:00
(1 row)
insert into test (f) values (now());
INSERT 0 1
SELECT * from test;
f
------------------------------
2005-04-25 00:28:33.34721+02
(1 row)
select f, f::timestamp at time zone 'MEZ' from test;
f | timezone
------------------------------+------------------------------
2005-04-25 00:28:33.34721+02 | 2005-04-25 01:28:33.34721+02
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sun, Apr 24, 2005 at 04:34:31PM -0600, Michael Fuhr wrote: > > gnumed=> select version(); > > version > > --------------------------------------------------------------- > > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3 > > That *definitely* isn't anything I'd want. That version of PostgreSQL Well :-) > > gnumed=> create teable test (f timestamp with time zone); > > CREATE > > Does that really work in 7.1.3? Trying to create a "teable" fails This results from "script" logging backspaces into the log file instead of deleting characters in there and my not spotting that when cleaning up the log file. Nothing to do with the question at hand. > insert into test (f) values (now()); > INSERT 37830 1 > SELECT * from test; > f > ------------------------------- > 2005-04-24 22:25:51.669218+00 > (1 row) > > select f, f::timestamp at time zone 'MEZ' from test; > f | timezone > -------------------------------+---------------------------- > 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218 > (1 row) Huh ? This IS different time zones from the same timestamp within the same query, isn't it ?? The fact that different versions of PostgreSQL get it right or wrong in a variety of ways indicates that the logic may need to be fixed but does show that in principle it is quite possible. If that's not what you wanted to do then I did misunderstand your original question. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> The fact that different versions of PostgreSQL get it right or
> wrong in a variety of ways indicates that the logic may need
> to be fixed but does show that in principle it is quite
> possible.
7.1's version of AT TIME ZONE was so badly broken that it doesn't really
matter whether it accidentally failed to malfunction in your particular
test case. That's simply not relevant to later versions.
regards, tom lane
On Mon, Apr 25, 2005 at 03:05:12AM -0400, Tom Lane wrote: > > The fact that different versions of PostgreSQL get it right or > > wrong in a variety of ways indicates that the logic may need > > to be fixed but does show that in principle it is quite > > possible. > > 7.1's version of AT TIME ZONE was so badly broken that it doesn't really > matter whether it accidentally failed to malfunction in your particular > test case. That's simply not relevant to later versions. Neither am I complaining about 7.1 malfunctioning nor am I saying that the actual result matters. What I *am* saying is that - although some versions may be wrong or right in different ways - one can surely get values mapped to different time zones for one and the same stored timestamp out of the database in a single query. Which was what I thought the OP said wasn't possible. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Mon, Apr 25, 2005 at 08:28:47AM +0200, Karsten Hilbert wrote:
> > > gnumed=> create teable test (f timestamp with time zone);
> > > CREATE
> >
> > Does that really work in 7.1.3? Trying to create a "teable" fails
>
> This results from "script" logging backspaces into the log
> file instead of deleting characters in there and my not
> spotting that when cleaning up the log file. Nothing to do with
> the question at hand.
It doesn't matter in this particular case because we could see what
was intended (I honestly wondered if that was a typo that 7.1.3
accepted), but sometimes subtle differences can matter so it's
better to post code that others can copy and paste verbatim into
their database -- that way the corrections themselves don't introduce
different behavior.
> > select f, f::timestamp at time zone 'MEZ' from test;
> > f | timezone
> > -------------------------------+----------------------------
> > 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
> > (1 row)
>
> Huh ? This IS different time zones from the same timestamp
> within the same query, isn't it ??
No, it isn't. In the above example from 7.2.7 the second column
has no time zone specification whatsoever; if you cast it to timestamp
with time zone then you get the following:
select f, (f::timestamp at time zone 'MEZ')::timestamptz from test;
f | timezone
-------------------------------+-------------------------------
2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+00
(1 row)
There's only one time zone displayed here: +00. Those are different
times being displayed in the same time zone, not the same time being
displayed in different time zones. Other versions of PostgreSQL
give varying output, but they all show both columns in the same
time zone.
The problem I was discussing involves getting *different* time zone
specifictions in the output. That is, something like this (which
apparently is possible in 7.1.3 but not in later versions):
f | timezone
-------------------------------+-------------------------------
2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01
> The fact that different versions of PostgreSQL get it right or
> wrong in a variety of ways indicates that the logic may need
> to be fixed but does show that in principle it is quite
> possible.
Certainly it's possible in principle; the question is how to do it
in practice in modern versions of PostgreSQL. As far as I can tell
it's not possible (short of changing the type to text, as one of
my earlier examples did), but again, I'd be pleased to be corrected
(preferably with an example that works in 8.0.2).
BTW, this is mostly academic to me, but others have asked similar
questions in the past so I've been curious about whether it could
be done.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
> > > select f, f::timestamp at time zone 'MEZ' from test;
> > > f | timezone
> > > -------------------------------+----------------------------
> > > 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
> > > (1 row)
> >
> > Huh ? This IS different time zones from the same timestamp
> > within the same query, isn't it ??
>
> No, it isn't. In the above example from 7.2.7 the second column
> has no time zone specification whatsoever;
You are correct.
However, how about this:
version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)
=> select now() as "MESZ", now() at time zone 'MEZ' as "MEZ", now() at time zone 'UTC' as "UTC" ;
MESZ | MEZ | UTC
------------------------+------------------------+------------------------
2005-04-25 17:13:19+02 | 2005-04-25 16:13:19+01 | 2005-04-25 15:13:19+00
(1 row)
Is that eventually it ?
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Michael Fuhr <mike@fuhr.org> writes:
> The problem I was discussing involves getting *different* time zone
> specifictions in the output. That is, something like this (which
> apparently is possible in 7.1.3 but not in later versions):
> f | timezone
> -------------------------------+-------------------------------
> 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01
It was not really possible in 7.1 either (nor any previous version).
The secret to the above is that in 7.1 the timezone() function's
result is *text*, not a timestamp object. So it's purely a display
artifact ...
regards, tom lane
On Mon, Apr 25, 2005 at 11:36:23AM -0400, Tom Lane wrote: > > -------------------------------+------------------------------- > > 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01 > > It was not really possible in 7.1 either (nor any previous version). > The secret to the above is that in 7.1 the timezone() function's > result is *text*, not a timestamp object. So it's purely a display > artifact ... Aha, there's the explanation. Thanks, Tom. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346