Convert text to timestamp in redshift

Question

I have a text field "presence_changed_at" with text values i.e. '2014/12/17 08:05:28 +0000. I need to convert this into timestamp. In postgreSQL there is function TO_TIMESTAMP(), however in redshift this does not seem to be supported. I can get the date without time by

TO_DATE("presence_changed_at",'YYYY/MM/DD HH24:MI:SS')

which produces

2014-12-12

but i can't find any way to get TIMESTAMP format.

Thanks in advance for solving this

Did you try COVERT ... as timestamp? Please note that it is better to do the conversion as part of the COPY command over as part of the queries. — Guy, Jul 27 '15 at 04:17

score 11 · Answer 1 · answered Nov 17 '17 at 15:41

11

cast(column as timestamp)

worked for me on redshift. See http://devdocs.io/postgresql~9.4/sql-expressions#SQL-SYNTAX-TYPE-CASTS

answered Nov 17 '17 at 15:41

Harry Moreno

10,231
7
64
116

score 4 · Answer 2 · answered Sep 02 '15 at 12:31

4

try convert function:

convert(timestamp,presence_changed_at)

answered Sep 02 '15 at 12:31

user3600910

2,839
4
22
36

1

Thank you for suggesting `CONVERT()`; however, this function only works when the string is in the very format implicit to the host (the default string format). I'd like to find something similar to `TO_TIMESTAMP()` so that I can pass it an arbitrary date format. – mjv Apr 03 '16 at 03:15

systemjack · Answer 3 · 2016-06-29T02:26:40.927

You can cast it to a timestamp. This supports quite a few reasonable formats without having to specify one.

select '2014/12/17 08:05:28 +0000'::timestamp;

      timestamp
---------------------
 2014-12-17 08:05:28

select '2014-12-02T05:00:00'::timestamp;
      timestamp
---------------------
 2014-12-02 05:00:00

select '2014-12-02T05:00:00PM'::timestamp;
      timestamp
---------------------
 2014-12-02 17:00:00

score 3 · Answer 4 · answered May 10 '16 at 23:05

3

It's surprisingly horrible to get a datetime from a unixtime in redshift without a subselect. However, you can do this:

select timestamptz 'epoch' + YOURTIME * interval '1 second'

answered May 10 '16 at 23:05

John Haugeland

9,230
3
37
40

You can also do TIMESTAMP if your times are all stored in UTC and you don't use TIMESTAMPTZ columns – ZiggyTheHamster Jun 28 '17 at 01:47

score 3 · Answer 5 · answered Nov 08 '18 at 19:40

Below functions worked for me correctly... Use whichever is applicable for you...

cast(column_name as timestamp)

to_date(column_name,'MM/DD/YYYY HH24:MI:SS')

to_date(column_name,'MM/DD/YYYY HH12:MI:SS')

to_timestamp(column_name,'MM/DD/YYYY HH24:MI:SS')

score 2 · Answer 6 · edited Jun 01 '16 at 09:51

2

Please use TO_Timestamp("presence_changed_at",'YYYY/MM/DD HH24:MI:SS') to get the desired output

edited Jun 01 '16 at 09:51

Nazik

8,696
27
77
123

answered Jun 01 '16 at 09:28

Saurabh Gautam

21
2

1

Helpful, but it appends a +00 since it's a TIMESTAMPTZ data type - what if my data is not in UTC? That +00 is misleading. – ScottEdwards2000 Jan 23 '17 at 20:57

score 2 · Answer 7 · answered Feb 13 '20 at 08:22

2

Use TO_TIMESTAMP(presence_changed_at,'YYYY/MM/DD HH24:MI:SS') which will return a TIMESTAMPTZ value, which can be cast to TIMESTAMP using ::TIMESTAMP.

answered Feb 13 '20 at 08:22

Shailesh

2,116
4
28
48

How to convert a timestamp string from one time zone to another. e.g. "YYYY/MM/DD HH24:MI:SS UTC" to a EST timezone. – Manoj Kumar Jun 20 '23 at 14:12
@ManojKumar use https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html – Shailesh Jun 21 '23 at 13:56

score 1 · Answer 8 · answered Jan 23 '17 at 10:20

1

As of today, to_timestamp is supported by redshift. So, your SQL query should work. Check http://docs.aws.amazon.com/redshift/latest/dg/r_TO_TIMESTAMP.html

answered Jan 23 '17 at 10:20

Dheeraj M R

61
1
5

score 0 · Answer 9 · answered Apr 03 '16 at 06:12

I recently worked on a database where a date & time variable was stored as text in a VARCHAR type, in multiple different formats (don't ask...), and had to convert it to a TIMESTAMP type. Since there is no TO_TIMESTAMP() function in Redshift, I used the trick suggested by Yiyu Jia on his [blog][1]. In a nutshell, the trick is to

use TO_DATE() to get the date converted
append the time portion of the input text to the above
CAST the resulting string to a TIMESTAMP

For example here's the snippet to deal with a field named myDate with dates in either of the following formats

"Feb 8 2013 10:06PM"
"25/09/2007 16:21:00"

It is rather heavy, but works. A regex test is used to test if the date corresponds to the format handled on a given line. (that is only necessary when dealing with multiple possible formats)
The 'Feb 0 2013' case is a bit more complicated because I remove the time portion of the text, before submitting it to TO_DATE(), and because another regex is used to extract the time portion that is appended (as opposed the simpler SUBSTRING() used for the same purpose, in the other case).

... ,
CASE
  -- Special date indicating "date not available": replaced by NULL
  WHEN myDate = '31/12/9999 23:59:59' OR myDate = 'Dec 31 9999 11:59PM' THEN NULL 
  -- 'Feb  8 2013 10:06PM' case
  WHEN myDate ~ '^[JFMASOND][a-z]{2}'  THEN
      CAST(TO_DATE(REGEXP_REPLACE(myDate , '\\s[0-9]{1,2}:[0-9]{2}[AP]M$', ''), 'Mon FMDD YYYY') || REGEXP_REPLACE(myDate , '[JFMASOND][a-z]{2}\\s+[0-9]{1,2}\\s+[0-9]{4}\\s+', ' ') AS TIMESTAMP)
  -- '25/09/2007 16:21:00' case
  WHEN myDate ~ '^[0-9]{2}/[0-9]{2}/[0-9]{4} '  THEN
      CAST(TO_DATE(myDate , 'DD/MM/YYYY HH24:MI:SS') || SUBSTRING(myDate FROM 11) AS TIMESTAMP)
  ELSE NULL
END AS MyNiceTimeStamp, 
...

  [1]: http://yiyujia.blogspot.com/2014/04/redshift-convert-integer-to-timestamp.html

putting a case in has severe performance implications. the regexp has severe performance and correctness implications. — John Haugeland, May 10 '16 at 23:05

score 0 · Answer 10 · answered May 23 '22 at 17:10

0

If datecolumn format is "2021-01-13T12:14:43Z" (varchar)

For converting to timestamptz datatype:

to_timestamp(datecolumn, 'yyyy-mm-dd hh:mi:ss')

For converting to timestamp datatype:

cast(datecolumn as timestamp)

Both will result in same output format "2021-01-13 12:14:43.000000"

answered May 23 '22 at 17:10

Vivek Ramanathan

421
5
4

how to convert a time only string to time. e.g. '17:30:43' is a time without date. I want to convert it to time datatype. https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-time – Manoj Kumar Feb 20 '23 at 08:52
I am assuming you also have a date string since the time string alone wouldn't make sense ? In that case you can always combine these strings and then perform the conversion to timestamp. Post this you can further split the date & time using something like this: https://docs.aws.amazon.com/redshift/latest/dg/SPLIT_PART.html – Vivek Ramanathan Mar 13 '23 at 20:51
No, I only have the time. There is no date. – Manoj Kumar Mar 20 '23 at 18:49
in that case you can just create a placeholder date string like '2023-01-31' and combine it with your time string and then go from there. – Vivek Ramanathan Mar 21 '23 at 14:50

Convert text to timestamp in redshift

10 Answers10

Linked