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

Clemons

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. – Marlysmarmaduke

Answer 1

cast(column as timestamp)

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

Foe

Answer 2

try convert function:

convert(timestamp,presence_changed_at)

Tyrosine

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. – Automatism

Answer 3

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

Bloodletting

Answer 4

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'

Dodge

You can also do TIMESTAMP if your times are all stored in UTC and you don't use TIMESTAMPTZ columns – Schmid

Answer 5

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')

Kermie

Answer 6

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

Ronnyronsard

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. – Chapfallen

Answer 7

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.

Kristoferkristoffer

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. – Vulgarian

@ManojKumar use docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html – Kristoferkristoffer

Answer 8

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

Bustamante

Answer 9

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

Automatism

putting a case in has severe performance implications. the regexp has severe performance and correctness implications. – Dodge

Answer 10

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"

Hospitalization

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. docs.aws.amazon.com/redshift/latest/dg/… – Vulgarian

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: docs.aws.amazon.com/redshift/latest/dg/SPLIT_PART.html – Hospitalization

No, I only have the time. There is no date. – Vulgarian

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. – Hospitalization