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

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