Re: Yet Another Timestamp Question: Time Defaults

On 01/21/2013 03:53 PM, Steve Crawford wrote:
> 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.)

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.

> Cheers,
> Steve
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com