CODEWITHSUNDEEP
amazon-redshiftto-dateto-timestamp
user5157294
user5157294

Reputation: 171

Convert text to timestamp in redshift

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

Upvotes: 17

Views: 66532

Answers (10)

Vivek Ramanathan
Vivek Ramanathan

Reputation: 470

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"

Upvotes: 0

Shailesh
Shailesh

Reputation: 2276

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.

Upvotes: 2

karthik G
karthik G

Reputation: 429

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

Upvotes: 4

Harry Moreno
Harry Moreno

Reputation: 11603

cast(column as timestamp)

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

Upvotes: 11

Dheeraj M R
Dheeraj M R

Reputation: 61

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

Upvotes: 1

systemjack
systemjack

Reputation: 2985

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

Upvotes: 4

Saurabh Gautam
Saurabh Gautam

Reputation: 21

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

Upvotes: 2

John Haugeland
John Haugeland

Reputation: 9668

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'

Upvotes: 3

mjv
mjv

Reputation: 75115

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

Upvotes: 0

user3600910
user3600910

Reputation: 3109

try convert function:

convert(timestamp,presence_changed_at)

Upvotes: 4

Related Questions