Skip site navigation (1) Skip section navigation (2)

Re: How do I convert a timestamp with time zone to local time?

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: How do I convert a timestamp with time zone to local time?
Date: 2008-07-29 19:49:47
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D9A2185@server.rad-con.local (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Thank you very much, sir.

After posting I realized that my question did not cover my problem.  I
also need to calculate if a given time is within daylight savings time
or not.  

The actual situation is this:  I have a table that contains the time at
which an event occurred and an estimate of how long it will be before a
second event occurs.  That interval is likely to be in the range of 2-3
days.  I need to handle the case in which that span includes the moment
at which daylight savings time status changes.  The table stores the
event time in both local time and UTC time.  So, I need a way to
calculate whether the time at the end of the interval will be DST or

I also have a table named "sys_info" that contains a single record for
system-wide information.  I can expand that table if I want.  The
easiest way I can think of to do what I need is merely to store the DST
start and end dates in that table, and then see if the date I'm checking
is between them or not.  But that won't work either, since the dates
change.  In the US, DST runs from the second Sunday in March to the
first Sunday in November.  I can hard-code to that rule (which is what
I'm doing for now), but I can't very well store it in a database.  And
do other countries use DST, and if so, how do they define it?  

I think that, if I'm careful, I can write my function so I'll be
converting everything I need to UTC before I start, and then I won't
convert anything back until the very end, and I'll never have to worry
about whether I'm spanning the DST change or not.  

It would still be nice to have a function that will check DST for a
given date, but I hope I can avoid needing it.

Thanks again!


In response to


pgsql-general by date

Next:From: Rob RichardsonDate: 2008-07-29 20:24:08
Subject: How do I set up automatic backups?
Previous:From: Francisco ReyesDate: 2008-07-29 19:34:32
Subject: Re: Index creation and maintenance_work_mem

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group