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 must be missing something. I to am in PST:

test=# \d ts_test
             Table "utility.ts_test"
  Column |           Type           | Modifiers
--------+--------------------------+-----------
  ts_fld | timestamp with time zone |


test=# INSERT INTO ts_test VALUES('2012-01-21');

test=# SELECT * from ts_test ;
          ts_fld
------------------------
  2012-01-21 00:00:00-08

test=# set timezone ='AKST9AKDT';

test=# SELECT ts_fld   from ts_test;
          ts_fld
------------------------
  2012-01-20 23:00:00-09


>
> Cheers,
> Steve
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com