CODEWITHSUNDEEP
sqlstringpostgresqldatetime
Ted
Ted

Reputation: 1760

Convert time and date to timestamp

I have the following date:

Wed Sep 16 2020 14:23:49 GMT+0000 (Coordinated Universal Time)

How I can convert it to timestamp?

case:

'Wed Sep 16 2020 14:23:49 GMT+0000 (Coordinated Universal Time)'::timestamp

doesn't works.

Upvotes: 0

Views: 133

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

A simple conversion works:

select 'Wed Sep 16 2020 14:23:49 GMT+0000'::timestamp

Or if you want the timezone:

select 'Wed Sep 16 2020 14:23:49 GMT+0000'::timestamptz

You can just take the initial portion:

select left('Wed Sep 16 2020 14:23:49 GMT+0000', 33)::timestamp

Upvotes: 0

Adrian Klaver
Adrian Klaver

Reputation: 19742

Assuming you are working with the entire string as given:


select  to_timestamp('Wed Sep 16 2020 14:23:49 GMT+0000 (Coordinated Universal Time)', 'Dy Mon DD YYYY HH24:MI:SS GMT TZH')::timestamp;

to_timestamp     
---------------------
 2020-09-16 07:23:49

I'm in PDT so the time got rotated to that.

For more information on to_timestamp and the meaning of the elements of the format string, see here:

https://www.postgresql.org/docs/current/functions-formatting.html

Upvotes: 1

GMB
GMB

Reputation: 222702

Just cast()!

select cast('Wed Sep 16 2020 14:23:49 GMT+0000' as timestamp)
select 'Wed Sep 16 2020 14:23:49 GMT+0000'::timestamp

Or, if you prefer to generate a timestamp with time zone:

select cast('Wed Sep 16 2020 14:23:49 GMT+0000' as timestamp with time zone)
select 'Wed Sep 16 2020 14:23:49 GMT+0000'::timestamp with time zone

Demo on DB Fiddle

Upvotes: 0

Related Questions