Thread

1. 'epoch'::timestamp and Daylight Savings

   Hosen, John <john.hosen@capita.co.uk> — 2002-10-22T16:11:35Z

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

2. Re: 'epoch'::timestamp and Daylight Savings

   lockhart@fourpalms.org — 2002-10-23T15:06:09Z

   > 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