Re: epoch from date field

At 7/5/02 11:43 AM, Laurette Cisneros wrote:

>Actually, we use tzset() to set the timezone. We're not operating in GMT
>at all, but it returns GMT.
>
>This, however does work returning the epoch for the current timezone:
>
>select extract(epoch from map_date::timestamp);
>
>So, for some reason extract won't convert a date to timestamp when it's
>passed in?

Dates are an integer representing the number of whole days since the
epoch, which was midnight UTC 1970-01-01. There is no such thing as a
fractional date, so by definition, a date must increment at midnight UTC
each day.

When you convert your date to a number of seconds elapsed since the
epoch, the result must be an even multiple of 86400 seconds.
Conceptually, the nonexistent time part of a "date" type is 00:00:00 UTC.
There is no way to have a date type represent midnight in UTC-7, because
that would be a fractional date in UTC.

So that's why you're seeing a "date" return midnight UTC; it's
calculating the time to an even multiple of 86400 seconds, which is the
finest granularity offered by the "date" type.

Now, if you convert your date to a timestamp instead, then you don't have
to live with the whole-day limitations of the date type. With a
timestamp, you're telling it that the date given is NOT a whole number of
days in UTC: instead, you're saying that it represents midnight in your
current timezone to the nearest millisecond, and PostgreSQL is then free
to use that exact time.

The implications of this are that '2002-07-03'::date does NOT represent
the same moment in time as '2002-07-03'::timestamp (unless your timezone
is the same as UTC). Given that, you can see why it would be a bad idea
to convert between the two automatically.

I found some useful information about why dates and timestamps are
intentionally different types, useful for different purposes, at:

http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

... in the section titled "Q. Which do I want to use: DATE or
TIMESTAMP? I don't need minutes or hours in my value".

Hope that helps.

------------------------------------
Robert L Mathews, Tiger Technologies