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

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. 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.

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.

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. :)

Regards
Gavan Schneider