Re: [GENERAL] 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
Re: [GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column
On 09/21/2016 08:37 PM, Patrick B wrote: Hi guys, 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 How can I do that without modifying any column/data? Maybe in a select? test=# select '2015-12-18 02:40:00'::date, '2015-12-18 02:40:00'::timestamp - '2015-12-18 02:40:00'::date AS time_val; date| time_val +-- 2015-12-18 | 02:40:00 Thanks! Patrick -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column
On Thu, Sep 22, 2016 at 1:37 PM, Patrick B wrote: > Hi guys, > > 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 > > How can I do that without modifying any column/data? > Maybe in a select? > postgres=# select to_char(start, 'DD-MM-') from test; to_char 18-12-2015 Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column
On 22-09-2016 12:37, Patrick B wrote: > Hi guys, > > 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 > > How can I do that without modifying any column/data? > Maybe in a select? TO_CHAR() is your friend: https://www.postgresql.org/docs/current/static/functions-formatting.html Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general