Re: Yet Another Timestamp Question: Time Defaults

On 01/21/2013 11:27 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
>> On 01/21/2013 07:26 AM, Rich Shepard wrote:
>>> What is the behavior if a column data type is timestamptz but there is
>>> only the date portion available? There must be a default time; can that be
>>> defined?
>
>> Easy enough to test:
>
>> test=# create table ts_test(ts_fld timestamp with time zone);
>> CREATE TABLE
>
>> test=# insert into ts_test VALUES ('2013-01-21');
>> INSERT 0 1
>
>> test=# SELECT * from ts_test ;
>> ts_fld
>> ------------------------
>> 2013-01-21 00:00:00-08
>
> 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?

>
> regards, tom lane
>

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