Обсуждение: Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

Поиск
Список
Период
Сортировка

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

От
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

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

От
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

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

От
Venkata B Nagothi
Дата:


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

От
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.klaver@aklaver.com

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

От
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