Re: [GENERAL] Thoroughly confused about time zones
On Feb 28, 2011, at 8:06 AM, Rob Richardson wrote: But if PostgreSQL doesn’t store time zones internally, then that difference is going to be 24 hours, which doesn’t help me. No, postgres stores timestamptz as UTC, so that calculation will work exactly like you want. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thoroughly confused about time zones
I think maybe I'm making things much more difficult than they need to
be. I tried this:
select cast (extract(epoch from ('2010-3-14 12:00'::timestamp -
'2010-3-13 12:00'::timestamp)) as integer) / 60 / 60
and got 24. The difference between timestamps without time zones is 24
hours, even though the interval spanned a time change.
But when I tried this:
select cast (extract(epoch from ('2010-3-14 12:00'::timestamptz -
'2010-3-13 12:00'::timestamptz)) as integer) / 60 / 60
I got 23, showing that even if I did not specify what time zone I'm
talking about, I got the correct answer.
RobR
Re: [GENERAL] Thoroughly confused about time zones
On Feb 28, 2011, at 8:18 AM, Rob Richardson wrote:
But when I tried this:
select cast (extract(epoch from ('2010-3-14 12:00'::timestamptz - '2010-3-13
12:00'::timestamptz)) as integer) / 60 / 60
I got 23, showing that even if I did not specify what time zone I’m talking
about, I got the correct answer.
You didn't specify the time zone, so it used your local time zone info--but not
just your current offset from UTC, rather the offsets from UTC at the
dates/times specified.
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thoroughly confused about time zones
On Mon, Feb 28, 2011 at 10:06:28AM -0500, Rob Richardson wrote: Our application stores the times at which several events happened, and we need to be able to calculate the elapsed time between events. Currently, the times are stored as timestamps without time zone, in both local and UTC times. Elapsed time calculations are based on the UTC times. Supposedly, that should avoid problems posed by the change from standard to daylight savings time, but it is not working out that easily in practice. A useful way I find of thinking about it is: you have two things you want to be able to store. - An instant in time, an event for example. The representation of this instant is dependant on where you are. This is the timestamp with time zone. - The wall clock time, what it say on the wall. So, no time zone, it just represents what a clock said at some point. This is the timestamp without time zone. The latter is usually not that useful, except for output. What you usually want is the timestamptz. Hop this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
