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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
