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

On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote:

>Adrian Klaver wrote: [Actually Gavan Schneider wrote this, don't blame Adrian :]
>
>>I see where my confusion lies. There are two proposals at work in the above:
>>
>>"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 "
>>
>>For the timestamp(alias for timestamp without time zone) case
>>the date does not change. For timestamp with time zone it might.
>
>Well, the big problem here is in trying to use either version of
>timestamp when what you really want is a date. It will be much
>easier to get the right semantics if you use the date type for a
>date.
>
This is the cleanest solution.

And I did not want to imply the following...

Adrian Klaver wrote:
>
>If I was following Gavan correctly, he wanted to have a single
>timestamp field to store calender dates and datetimes. In other
>words to cover both date only situations like birthdays and
>datetime situations like an appointment.

My discussion really only applies to some notion of the best
(or, more exactly, the least wrong) time to attribute to a date
when conversion to timestamp happens for whatever reason. And,
as indicated in my original post, I have been stung when dates
got (badly) mixed into a datetime timezone aware context.

The points raised by Adrain have prompted some more research on
my part and I am intrigued to learn that on one day of the year
in many countries (e.g., Brazil) where daylight conversion
happens over midnight the local-time version of midnight as
start of day does not exist. Basically the last day of
unadjusted time ends at midnight and rolls directly into
01:00:00 the next day (i.e., time 00:00:00 never happens on this
one day). So the current date-> date+time system must already
have some added complexity/overhead to check for this rare
special case. (If not, there's a bug needs fixing!)

Basically midnight is not safe as a target entity once timezones
and daylight saving get involved. Midday, on the other hand, is
a very solid proposition, no checks required, 12:00:00 will
happen in all time zones on every day of the year! Basically
nobody messes with their clocks in the middle of the day.

So restating:
     '2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never
be wrong; but,
     '2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in
some places.

Regards
Gavan Schneider