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
