Re: Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

On 9/21/2016 8:37 PM, Patrick B wrote:
> I'm using postgres 9.2 and got the following column:
>
> start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL
>
>
> SELECT start FROM test1;
>
>
> 2015-12-18 02:40:00
>
> I need to split that date into two columns on my select:
>
> 2015-12-18 = date column
> 02:40:00 = time column

select start::date as date_column start::time as time_column from ....

BUT, you really gotta watch out for type conversions around timestamp
WITHOUT time zone, as most conversions expect TIMESTAMP WITH TIME ZONE,
which internally are stored in UTC but are converted from/to
CLIENT_TIMEZONE on input output. so timestamp without time zone can get
converted to timestamp with time zone, then converted to date or time,
and get all messed up.

--
john r pierce, recycling bits in santa cruz