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
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
Import Notes
Resolved by subject fallback
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_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
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_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/
Show quoted text
Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle
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
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 one is using the template, but no one is using it.
I'm using PgAdmin III ver 1.1.0. Has some one seen something like this?
/Paul
________________________________
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 one is 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