Re: help building datetime from varchars - Mailing list pgsql-general

From Jean-Luc Lachance
Subject Re: help building datetime from varchars
Date
Msg-id 4097A7E1.4000708@sympatico.ca
Whole thread Raw
In response to help building datetime from varchars  (Brent Wood <b.wood@niwa.co.nz>)
List pgsql-general
You 30 minutes are getting converted to 30 months.
Use DD/MM/YYYYHH24:MI:SS instead.



Brent Wood wrote:

> Hopefully someone can point me in the proper direction....
>
> I have a table containg (amongst others) two varchar attrs date_s &
> time_s.
>
> They contain strings like:
>
>    date_s   |  time_s
> ------------+----------
>  01/10/1989 | 00:30:00
>
> Can someone suggest an sql to turn these into a single datetime?
>
>
>
>
> I have tried the following with results I don't understand:
>
> env2003=# select date_s || time_s, to_timestamp(date_s || time_s,'DD/MM/YYYYHH:MM:SS') from event limit 1;
>       ?column?      |      to_timestamp
> --------------------+------------------------
>  01/10/198900:30:00 | 1991-06-05 00:00:00+12
> (1 row)
>
> env2003=# select date_s || ' ' || time_s, to_timestamp(date_s || ' ' || time_s,'DD/MM/YYYY HH:MM:SS') from event
limit1; 
>       ?column?       |      to_timestamp
> ---------------------+------------------------
>  01/10/1989 00:30:00 | 1991-06-05 00:00:00+12
> (1 row)
>
>
>
> Thanks,
>
>    Brent Wood
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>


pgsql-general by date:

Previous
From: "Shane D."
Date:
Subject: [Fwd: help building datetime from varchars]
Next
From: Michal Hlavac
Date:
Subject: Variable in PostgreSQL 7.4.x