Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
Karsten Hilbert [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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 [EMAIL PROTECTED]:~ 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 [EMAIL PROTECTED]:~ 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
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/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
