Thread: Thoroughly confused about time zones
Greetings!
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.
I thought that I could convert the non-timestamped value to a UTC time by using the “AT TIME ZONE” clause, but I see now that that is still using the local time zone. I want to convert '2010-03-14 12:00 CDT' to UTC, but the result I get from
select into UTCTimestampTZ CurrentTimestampTZ at time zone 'UTC';
is
NOTICE: UTCTimestampTZ: 2010-03-14 17:00:00-04
(The example time came from someone in Indiana, in the Central time zone, and I’m in the Eastern time zone.)
The result I want is 2010-03-14 17:00:00-00. Is there any way to get that?
But then, in another forum, I found this:
I do realize that currently timestamptz doesn't store the timezone
offset/timezone name (thus timestamp and timestamptz both require the
same amount of storage, 8 bytes).
I didn’t realize that. If that’s true, then the time zone is basically meaningless, only telling the user what his current time zone is.
I had thought that it would be good for us to modify our application to use only timestamped values. Then, we’d get an event startong at 2010-3-13 12:00-05 (before daylight savings time in the Eastern time zone) and ending at 2010-3-14 12:00-04, and PostgreSQL would correctly see the difference in time zones and report the difference as 23 hours (since we sprang ahead in the spring time change). But if PostgreSQL doesn’t store time zones internally, then that difference is going to be 24 hours, which doesn’t help me.
So what is the best way to calculate the elapsed time between two times spanning a change from standard to daylight savings time, or the reverse?
Thank you very much.
RobR
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 helpme. No, postgres stores timestamptz as UTC, so that calculation will work exactly like you want. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
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
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,
ratherthe offsets from UTC at the dates/times specified.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
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 <kleptog@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
Attachment
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rob Richardson
Sent: den 28 februari 2011 16:19
To: pgsql-general@postgresql.org
Cc: Tim Kelly
Subject: 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
On Mar 1, 2011, at 12:07 PM, Malm Paul wrote: > Hi, I've used PgAdmin III to store a server backup. But I'm not able to restore it. > > Please, could any one tell me how to do it? Im using version 1.10 > /Paul Following link would help for restoring backup: http://www.pgadmin.org/docs/1.10/backup.html Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
On 01/03/2011 06:37, Malm Paul wrote: > Hi, I've used PgAdmin III to store a server backup. But I'm not able to > restore it. > Please, could any one tell me how to do it? Im using version 1.10 Hi there, Did you create a text or binary backup? If binary, you either (i) use pg_restore on the command line, or (ii) use pgAdmin's "restore" option from the menu you get when right-clicking on the tree view (which just uses pg_restore anyway). If you created a text backup, you'll need to feed it to psql on the command line: psql -f <backup-file.sql> <database-name> Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Le 01/03/2011 07:42, Malm Paul a écrit : > Hi, > I'm trying to create a new database by using a template database. But it is not possible. The error code is that some oneis using the template, but no one is using it. I would bet *you* are connected with pgadmin to the template1 database. > I'm using PgAdmin III ver 1.1.0. Has some one seen something like this? I really hope you're not using 1.1.0. I've never seen this release and, according to its number, it may be 6 years old :) You probably meant 1.10.0. -- Guillaume http://www.postgresql.fr http://dalibo.com