Thread: 'epoch'::timestamp and Daylight Savings
Hi there all PostgreSQL Hackers.
We have just upgraded from 7.1.3 to 7.2.3 over the weekend, and have just
noticed something weird with regards 'epoch'::timestamp.
In 7.1.3, 'epoch'::timestamp always returned the value 1970-01-01
00:00:00+00, following the upgrade we now get:-
timestamptz
------------------------1970-01-01 01:00:00+01
Also, any fields set to 'epoch'::timestamp before the upgrade now return:-
e_app_xfer_date
------------------------1970-01-01 00:00:00+01
If we issue a SET TIMEZONE TO 'GMT' the results are the same as for the
previous release.
This is causing us a problem as we use epoch to indicate whether the data
has been processed, and to log the date and time of processing.
We are running on RedHat 7.1 with kernel 2.4.9-21smp, and glibc
glibc-2.2.4-19.3 with PostgreSQL compiled from the source tarfile.
Output from version(): PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by
GCC 2.96
With regards PostgreSQL in general, we have been using it since version
6.4.x and have found it (even the earier versions) extremely reliable an
powerful. Keep up the good work guys!
John Hosen
Senior Network Analyst
Capita RAS
http://www.capitaras.co.uk/
***********************************************************************
This email and any files attached to it are confidential and intended
solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify
the system manager.
The message and any files attached to it have been scanned by
MIMEsweeper with Sophos Sweep and found to be free from all known
viruses.
Information on MIMEsweeper can be found at http://www.mimesweeper.com/
***********************************************************************
Thomas,
Thanks for replying so quickly.
Upon some further investigation, the conversion of the time to BST is OK on
data inserted before the upgrade (the display was just a red herring), the
problem is just on new data inserted into the table with a default value for
the timestamp fields.
The output from \d on the table is below:
capitaras_live=# \d e_application Table "e_application" Column |
Type |
Modifiers
-------------------+--------------------------+-----------------------------
---------------------------------------e_app_id_num | integer | not null default
nextval('"e_application_e_app_id_num_seq"'::text)e_app_cand_id_num | integer | e_app_job_id |
charactervarying(30) | not nulle_app_cid_num | bigint | e_app_cts | character
varying(30) | e_app_mts | character varying(30) | e_app_print_date | timestamp with time zone | default
"timestamp"('epoch'::text)e_app_xfer_date | timestamp with time zone | default
"timestamp"('epoch'::text)e_app_completed | character varying(6) | e_app_xml | text |
e_app_modified | timestamp with time zone | e_app_created | timestamp with time zone | e_app_wd_feedback | text
| e_app_submitted | timestamp with time zone |
Indexes: e_app_completed_idx, e_app_job_id_and_completed_idx
Primary key: e_application_pkey
Triggers: RI_ConstraintTrigger_27961
With a bit more playing, it definitely looks like something wrong with the
way the default value has been set follwing the export & import for the
upgrade:-
Create table arnold (a int8,b timestamp default 'epoch'::timestamp,c timestamp default
"timestamp"('epoch'::text)
);
capitaras_test=# \d arnold Table "arnold"Column | Type |
Modifiers
--------+--------------------------+----------------------------------------
-------a | bigint | b | timestamp with time zone | default '1970-01-01
01:00:00+01'::timestamptzc | timestamp with time zone | default "timestamp"('epoch'::text)
capitaras_test=# insert into arnold (a) values (1);
INSERT 182907 1
capitaras_test=# insert into arnold (a) values (2);
INSERT 182907 1
capitaras_test=# set timezone to 'Europe/London';
SET VARIABLE
capitaras_test=# select * from arnold;a | b | c
---+------------------------+------------------------1 | 1970-01-01 01:00:00+01 | 1970-01-01 00:00:00+012 | 1970-01-01
01:00:00+01| 1970-01-01 00:00:00+01
(2 rows)
capitaras_test=# set timezone to 'GMT';
SET VARIABLE
capitaras_test=# select * from arnold
capitaras_test-# ;a | b | c
---+------------------------+------------------------1 | 1970-01-01 00:00:00+00 | 1969-12-31 23:00:00+002 | 1970-01-01
00:00:00+00| 1969-12-31 23:00:00+00
(2 rows)
I think that the best way forward for us (short of re-writing the backend to
use NULL) will be to just alter the default value to the one in column b in
the test table above.
With regards the disclaimer, don't you just love lawyers ;-)
Once again, thanks for replying so quickly.
Regards
John.
-----Original Message-----
From: Thomas Lockhart [mailto:lockhart@fourpalms.org]
Sent: 23 October 2002 16:06
To: Hosen, John
Cc: 'pgsql-hackers@postgresql.org'
Subject: Re: 'epoch'::timestamp and Daylight Savings
> We have just upgraded from 7.1.3 to 7.2.3 over the weekend, and have
> just noticed something weird with regards 'epoch'::timestamp. In
> 7.1.3, 'epoch'::timestamp always returned the value 1970-01-01
> 00:00:00+00,...
I would expect 'epoch'::timestamp to always act as though the value were
the same as Unix time zero. But it did not explicitly return that value:
lockhart=# select version();
------------------------------------------------------------- PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC
2.96
lockhart=# select 'epoch'::timestamp;
---------- epoch
lockhart=# select 'epoch'::timestamp + '0 sec'::interval;
------------------------ 1969-12-31 16:00:00-08
> following the upgrade we now get:-
> timestamptz
> ------------------------
> 1970-01-01 01:00:00+01
Which is consistant with the previous result in absolute time.
You will find that the most recent versions of PostgreSQL convert
'epoch' on input, and that with- and without time zone data types are
available:
lockhart=# select cast('epoch' as timestamp with time zone); timestamptz
------------------------ 1969-12-31 16:00:00-08
lockhart=# select cast('epoch' as timestamp without time zone); timestamp
--------------------- 1970-01-01 00:00:00
> Also, any fields set to 'epoch'::timestamp before the upgrade now return:-
> e_app_xfer_date
> ------------------------
> 1970-01-01 00:00:00+01
> If we issue a SET TIMEZONE TO 'GMT' the results are the same as for
> the previous release.
Not sure about this one. What is the schema? Can you give an example
where the time gets shifted by an hour?
> This is causing us a problem as we use epoch to indicate whether the
> data has been processed, and to log the date and time of processing.
Using "special values" to indicate status can be troublesome, as you are
finding. I'd suggest using NULL to indicate that a field is not known or
not yet set.
> This email and any files attached to it are confidential and intended
> solely for the use of the individual or entity to whom they are
> addressed. If you have received this email in error please notify
> the system manager.
Oops. Can you please confirm that we were the intended recipients? ;)
hth
- Thomas
This email has been scanned for all viruses by the MessageLabs SkyScan
service.
***********************************************************************
This email and any files attached to it are confidential and intended
solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify
the system manager.
The message and any files attached to it have been scanned by
MIMEsweeper with Sophos Sweep and found to be free from all known
viruses.
Information on MIMEsweeper can be found at http://www.mimesweeper.com/
***********************************************************************
"Hosen, John" <John.Hosen@capita.co.uk> writes:
> e_app_print_date | timestamp with time zone | default
> "timestamp"('epoch'::text)
Yeah, there's your problem. You are casting 'epoch' to type timestamp
without time zone, and thence to timestamp with time zone. The first
step gives "midnight" and the second assumes that that means "midnight
local time".
For example, in US EST zone I get:
regression=# select "timestamp"('epoch'::text); timestamp
---------------------1970-01-01 00:00:00
(1 row)
regression=# select ("timestamp"('epoch'::text))::timestamptz; timestamp
------------------------1970-01-01 00:00:00-05
(1 row)
whereas what is wanted is
regression=# select "timestamptz"('epoch'::text); timestamptz
------------------------1969-12-31 19:00:00-05
(1 row)
So you can fix the problem just by setting the default to be
'epoch'::timestamptz.
The problem is probably related to the renaming we've been carrying out
to get closer to the SQL spec: "timestamp" now means timestamp without
time zone, which is not what it meant in older Postgres releases.
regards, tom lane
> We have just upgraded from 7.1.3 to 7.2.3 over the weekend, and have just
> noticed something weird with regards 'epoch'::timestamp.
> In 7.1.3, 'epoch'::timestamp always returned the value 1970-01-01
> 00:00:00+00,...
I would expect 'epoch'::timestamp to always act as though the value were
the same as Unix time zero. But it did not explicitly return that value:
lockhart=# select version();
------------------------------------------------------------- PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC
2.96
lockhart=# select 'epoch'::timestamp;
---------- epoch
lockhart=# select 'epoch'::timestamp + '0 sec'::interval;
------------------------ 1969-12-31 16:00:00-08
> following the upgrade we now get:-
> timestamptz
> ------------------------
> 1970-01-01 01:00:00+01
Which is consistant with the previous result in absolute time.
You will find that the most recent versions of PostgreSQL convert
'epoch' on input, and that with- and without time zone data types are
available:
lockhart=# select cast('epoch' as timestamp with time zone); timestamptz
------------------------ 1969-12-31 16:00:00-08
lockhart=# select cast('epoch' as timestamp without time zone); timestamp
--------------------- 1970-01-01 00:00:00
> Also, any fields set to 'epoch'::timestamp before the upgrade now return:-
> e_app_xfer_date
> ------------------------
> 1970-01-01 00:00:00+01
> If we issue a SET TIMEZONE TO 'GMT' the results are the same as for the
> previous release.
Not sure about this one. What is the schema? Can you give an example
where the time gets shifted by an hour?
> This is causing us a problem as we use epoch to indicate whether the data
> has been processed, and to log the date and time of processing.
Using "special values" to indicate status can be troublesome, as you are
finding. I'd suggest using NULL to indicate that a field is not known or
not yet set.
> This email and any files attached to it are confidential and intended
> solely for the use of the individual or entity to whom they are
> addressed. If you have received this email in error please notify
> the system manager.
Oops. Can you please confirm that we were the intended recipients? ;)
hth
- Thomas
> Create table arnold (
> a int8,
> b timestamp default 'epoch'::timestamp,
> c timestamp default "timestamp"('epoch'::text)
> );
> I think that the best way forward for us (short of re-writing the backend to
> use NULL) will be to just alter the default value to the one in column b in
> the test table above.
Hmm. The "timestamp"() call is forcing TIMESTAMP WITHOUT TIME ZONE,
which then gets converted to TIMESTAMP WITH TIME ZONE (adding in your
one hour offset), which is the default for the unadorned, unquoted
'timestamp' type.
You can use something like
cast('epoch'::text as timestamp with time zone)
to get what you want, and can use "timestamptz"() if you insist. But
that is not recommended for direct use in schema definitions, even if
pgsql chooses to use it for dump/reloads at the moment.
- Thomas
Thomas & Tom,
Thanks for all your help, I have updated all the defaults to use timestamptz
and everything is working fine again.
We can't wait for 7.3, keep up the good work.
John
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 23 October 2002 19:47
To: Hosen, John
Cc: 'Thomas Lockhart'; 'pgsql-hackers@postgresql.org'
Subject: Re: [HACKERS] 'epoch'::timestamp and Daylight Savings
"Hosen, John" <John.Hosen@capita.co.uk> writes:
> e_app_print_date | timestamp with time zone | default
> "timestamp"('epoch'::text)
Yeah, there's your problem. You are casting 'epoch' to type timestamp
without time zone, and thence to timestamp with time zone. The first step
gives "midnight" and the second assumes that that means "midnight local
time".
For example, in US EST zone I get:
regression=# select "timestamp"('epoch'::text); timestamp
---------------------1970-01-01 00:00:00
(1 row)
regression=# select ("timestamp"('epoch'::text))::timestamptz; timestamp
------------------------1970-01-01 00:00:00-05
(1 row)
whereas what is wanted is
regression=# select "timestamptz"('epoch'::text); timestamptz
------------------------1969-12-31 19:00:00-05
(1 row)
So you can fix the problem just by setting the default to be
'epoch'::timestamptz.
The problem is probably related to the renaming we've been carrying out to
get closer to the SQL spec: "timestamp" now means timestamp without time
zone, which is not what it meant in older Postgres releases.
regards, tom lane
This email has been scanned for all viruses by the MessageLabs SkyScan
service.
***********************************************************************
This email and any files attached to it are confidential and intended
solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify
the system manager.
The message and any files attached to it have been scanned by
MIMEsweeper with Sophos Sweep and found to be free from all known
viruses.
Information on MIMEsweeper can be found at http://www.mimesweeper.com/
***********************************************************************