Re: Yet Another Timestamp Question: Time Defaults - Mailing list pgsql-general

On 01/21/2013 02:48 PM, Gavan Schneider wrote:
> On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
> ....
>> On 01/21/2013 11:27 AM, Tom Lane wrote:
>>> Note that that default is local midnight according to your current
>>> timezone setting (from which we may guess that Adrian lives on the US
>>> west coast, or somewhere in that general longitude).
>>>
>>>> Not sure you can change the default supplied by Postgres,
>>>
>>> "SET timezone" ought to do it ...
>>
>> I took Richs question to mean can you change the time portion
>> supplied by Postgres, so:
>>
>> Instead of '2013-01-21' having the time portion set to local midnight
>> it could be set to a user supplied value say, 08:00:00. That is not
>> possible, correct. In the absence of a time portion a date string
>> supplied to timestamp will always get local midnight?
>>
> Thanks to all for the discussion of timestamps with/without timezones
> I have been learning a lot from the side.
>
> Taking another tangent I would much prefer the default time to be
> 12:00:00 for the conversion of a date to timestamp(+/-timezone).
>
>     Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00
>
> The benefit of the midday point is that the actual date will not
> change when going through the timezone conversion.

Just like it doesn't change now? (I just checked against all of the more
than 1,100 zones in PG without seeing a problem.)

> This has implications for time-of-day insensitive data such as
> birthdays and other calendar values. I am still resolving "off by one
> day" errors that crept into many entries in my calendar and contacts
> from several years ago when data was added while travelling across
> multiple time zones (and I did report it as a bug back then). With
> this lesson learnt the workaround for me in my own applications since
> has been to store such dates as point-in-time for midday while keeping
> track of the input/output so it only gets used as a date... sometimes
> tedious, and a last resort. Mostly I have been actively avoiding
> anything with the taint of timezone due to this bad experience. It's
> time to reconsider, I guess, since this can cause other forms of silly
> behaviour.

Date/time is not trivial. The portions of the PostgreSQL manual dealing
with those data types bear careful and thoughtful reading and rereading
while you experiment at the same time in a psql terminal till it
"clicks." And while some time issues are universal, treatment varies
from program to program - especially regarding assumptions when the
input is ambiguous. I'm in the US Pacific time zone so without further
qualification, "2012-11-04 0130" could be 0130 PST or 0130 PDT.

The "date" program on my Linux desktop assumes daylight time:
date -d '2012-11-04 0130'
Sun Nov  4 01:30:00 PDT 2012

PostgreSQL assumes standard time:
select '2012-11-04 0130'::timestamptz;
       timestamptz
------------------------
  2012-11-04 01:30:00-08

Naturally this can lead to all sorts of "fun" when multiple technologies
are involved.

Meanwhile if I'm up at that hour and try to schedule a job for immediate
execution via "at now", the "at" program tells me it is "Cowardly
refusing to schedule a job in the past." So much for even internal
consistency.


>
>
> Aesthetically (and/or mathematically) the midday point is more
> accurate. It is the middle of the relevant interval (i.e., 24 hours)
> implied by a date. Midnight is the extreme edge of any date (i.e., not
> what you would consider as mid-target). "Midnight" also has confusing
> English semantics since it can belong to either of its adjacent days.
>

Except for days that are 23-hours long, or 25, or other (it's a big
world with all sorts of timezone rules). It's also very useful for
common queries (select ... from somelog where logtime > current_date)
and provides a known starting-point from which you can easily calculate
the offsets you desire.

BTW It's not at all "more accurate" - it is simply different definition.

> I don't know if the current behaviour will be deemed to be too rusted
> in place for change, or if this proposal has too many adverse
> consequences, but hope springs eternal. :)
>

It would sure break a lot of my queries. And for the many people who
want/expect the date to cast to date at 00:00:00 local time it would
lead to a load of pitfalls such as naively subtracting 12-hours or
requiring the programmer to add complexity to determine how many hours
to subtract based on local time zone and current date.

But you are, of course, free to use the capability that PostgreSQL gives
you to define pretty much any data-type you want along with your desired
casting rules if you so desire. Just don't expect the built-in
definitions to change.

Cheers,
Steve