0

I am facing an issue when using the function to_timestamp() on Aginity Workbench for AWS Redshift. For some reason I don't understand, 2 hours are added to the timestamp i am trying to create.

I have been looking for a solution but couldn't find anything similar, can someone help me solving this problem ?

The following code :

select
'2017-10-17 10:30:00' test,
to_timestamp('2017-10-17 10:30:00','YYYY-MM-DD HH24:MI:SS') test_converted

Retrieves :

test = 2017-10-17 10:30:00

test_converted = 2017-10-17 12:30:00

Pierre Dudek
  • 252
  • 4
  • 11
  • looks like a time zone thing. are you UTC+2? what do you get with '2017-10-17 10:30:00'::timestamp and '2017-10-17 10:30:00'::timestamptz and '2017-10-17 10:30:00'::timestamptz at time zone 'UTC' – Jon Scott Oct 23 '17 at 16:08
  • Agreed, '2017-10-17 10:30:00'::timestamptz gives '2017-10-17 12:30:00' and '2017-10-17 10:30:00'::timestamptz at time zone 'UTC' gives me '2017-10-17 10:30:00'. What are the "::" doing ? If XX::timestamp equals to_timestamp(XX,'YYYY-MM-DD HH24:MI:SS') then it fixes my problem. – Pierre Dudek Oct 23 '17 at 16:45
  • please see my answer below and "accept" if it is what you need – Jon Scott Oct 23 '17 at 17:22

1 Answers1

1

you can use :: to cast types in postgres and redshift, so your code would be

select '2017-10-17 10:30:00'::timestamp;

this would resolve your issue.

(I think your previous code was converting to local timezone)

Jon Scott
  • 4,144
  • 17
  • 29