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

2016-09-21 Thread John R Pierce 

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

2016-09-21 Thread Adrian Klaver 

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

2016-09-21 Thread Venkata B Nagothi 
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

2016-09-21 Thread Ian Barwick 
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

[GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

2016-09-21 Thread Patrick B 
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?

Thanks!
Patrick