Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Patrick B
Date:
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

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

From
Ian Barwick
Date:
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

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

From
Venkata B Nagothi
Date:


On Thu, Sep 22, 2016 at 1:37 PM, Patrick B <patrickbakerbr@gmail.com> 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-YYYY') from test;
  to_char
------------
 18-12-2015


Regards,
Venkata B N

Fujitsu Australia

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

From
Adrian Klaver
Date:
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.klaver@aklaver.com

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

From
John R Pierce
Date:
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