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 time
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 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 t
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
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.
