Search code examples
sqltimezoneprestotimezone-offsettimestamp-with-timezone

Error while converting timestamp string with timezone (+0000) to Timestamp in Presto


I am trying to convert a timestamp string to timestamp with date_parse, but keep getting an error. Any suggestions? I am working on Presto SQL. I also refered: http://teradata.github.io/presto/docs/127t/functions/datetime.html, but couldnt find anything that can deal with +0000 i.e Timezone.

I tried:

date_parse('2021-05-22 04:00:00 +0000', '%Y-%m-%d %h:%i:%s')

throws me error Invalid format: "2021-05-22 04:00:00 +0000" is malformed at "+0000""

Also tried this:

date_parse('2021-05-22 04:00:00 +0000', '%Y-%m-%d %h:%i:%s +Z')

throws me error Invalid format: "2021-05-22 04:00:00 +0000" is malformed at "0000""

Any help would be greatly appreciated, thanks!


Solution

  • This works!:

    SELECT PARSE_DATETIME('2017-03-29 10:32:28 +0000', 'YYYY-MM-dd HH:mm:ss Z');
    

    output: 2017-03-29 10:32:28.000 (timestamp_tz)