Re: definative way to place secs from epoc into timestamp - Mailing list pgsql-sql

On Fri, 2005-03-04 at 01:35, Michael Glaesemann wrote:
> 
> On Mar 4, 2005, at 14:47, Bret Hughes wrote:
> 
> > On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote:
> >> (Why are you using timestamp without time zone anyway? For recording  
> >> the
> >> time at which an event occurred that usage is simply wrong - in fact I
> >> can't see any situation in which a Unix epoch time can correctly be
> >> converted to a timestamp without time zone.)
> >>
> >
> > Valid question.  Because there is no reason to keep up with time zones
> > and the fact that  I want the same value from the data base that I put
> > into it.  The app that this db supports is written in  php and I kept
> > getting something different out than what I put into it in the other
> > passes I made while trying to get my head around this.  the timestamps
> > have historically been stored in flat files.
> 
> <snip />
> 
> > What goes in comes out.  Gotta like it.
> 
> I think the reason this works is because your webserver and your  
> postgresql server are in the same time zone, which is probably an  
> assumption made in a great-many cases. You may run into problems if at  
> some time the dbms and webserver are not in the same time zone and  
> you're relying on dbms-generated times (such as now() or  
> current_timestamp), or if the system
> is relocated to another time zone.
> 
> I think the following illustrates a problem that can occur if the  
> assumption that the time zone is not constant is no longer valid.
> 
> Your system is working for you, so that's great. I just wanted to  
> explore this for myself a bit more -- I find the time zone related  
> material hard to get my head around myself :). Since I went through it,  
> I thought I'd share it with the list.
> 

Thanks for the additional walk through.  Thanks also to everyone else
who has contributed to this thread and my education.  I think I finally
figured out what is what.  Part of my issue has been that there are so
many things that can affect the tz offset that is retrieved from the os
via php or some other language I was looking to eliminate one of them. 
Examples that "stayed" in psql were not helping me on that point.  Once
I realized that the simple solution was indeed to stay in UTC (using
gmmktime/gmstrftime rather than mktime/strftime in php for instance) and
everyone's constructive criticism finally hammered the point.

I have been humbled by this which my wife will tell you is not so bad a
thing.  I usually "get it" pretty quickly when tackling new concepts but
this whole deal took me much longer than usual.

I am working to alter the design now and since most of the db calls
involving timestamps are contained in two php classes I should have it
fixed this week end.

Thanks again for everyone's patience and help.

Bret