Extract timestamp from timestamp string in redshift

Question

I'm dealing with two redshift tables where each one have different formats of time stamps.

table 1 - start_date: 2013-07-26 07:30:17.0
table 2 - end_date: 20140517004819823Z

My requirement is to have both in the same format (like table 1, start date) so that i can use datediff to find the interval between them.

How do i format the table 2 end date similar to table 1. I've tried cast(end_date as timestamp) but it throws Invalid data code: 8001

Edit 1:

Tried Using to_timestamp(end_date, 'YYYY-MM-DD HH24:MI:SS', false) but it throws Invalid operation: value for "YYYY" in source string is out of range;

thanks John, but I'm stuck with "YYYY" in source string is out of range". Any idea how to fix this — Adam Smith
Please Edit your question to show the command that you are attempting to use, and the resulting error. — John Rotenstein
Your string is not formatted as YYYY-MM-DD HH24:MI:SS -- it looks more like YYYYMMDDHHMMSS and some other bits on the end. You will need to tell Redshift how the string is formatted so that it can convert it to a timestamp. Please read the linked documentation. — John Rotenstein
Correct, I tried to_timestamp(end_date, 'YYYYMMDDHH24MISS', false) and 20150425201043668Z is getting returned as 2015-04-26 08:17:48.0. Not sure if the timestamp is correct though. — Adam Smith

John Rotenstein John Rotenstein · Accepted Answer · 2021-08-02T22:41:38

You would use TO_TIMESTAMP function - Amazon Redshift:

to_timestamp('20150425201043668Z', 'YYYYMMDDHH24MISSMSZ', true)

This translates:

Date: YYYYMMDD
Hour: HH24
Minute: MI
Seconds: SS
Milliseconds: MS

Extract timestamp from timestamp string in redshift

1 Answers