1

Note below, the conversation has a positive result for "current_timestamp", whose format is "2021-05-13 01: 36: 29.104 UTC". Now if I analyze the last three examples, I got an error, I imagine it must be due to the "T" and "Z" in the middle of the timestamp. Why have timestamp with "T" and "Z", and another "" and "UTC" in Presto? How to resolve this conversation?

> Tests - Positive results

select current_timestamp as "Today's timestamp"

2021-05-13 02:15:43.209 UTC

select cast(current_timestamp as date)

2021-05-13

select date(current_timestamp)

2021-05-13

select date_format(current_timestamp, '%Y-%m-%d') as "Today's date"

2021-05-13

> Tests - Negative results

select cast(x.payment_date as date) FROM table_x x

INVALID_CAST_ARGUMENT: Value cannot be cast to date: 2021-03-19T00:00:00.000Z

select date(x.payment_date) FROM table_x x

INVALID_CAST_ARGUMENT: Value cannot be cast to date: 2021-03-19T00:00:00.000Z

select date_format(x.payment_date, '%Y-%m-%d') FROM table_x x

SYNTAX_ERROR: line 1:20: Column 'x.payment_date' cannot be resolved

Note: I analyzed the links below, but for these scenarios I didn't see any practical any solution.

How to convert timestamp to date in Presto?

https://prestodb.io/docs/current/functions/datetime.html

Martin Traverso
  • 4,731
  • 15
  • 24
Ezequielab
  • 21
  • 1
  • 2
  • 6

1 Answers1

1

It looks like your timestamps are in ISO8601 format. You can convert them to a date by:

  1. Converting them to a timestamp(p) with time zone using the from_iso8601_timestamp function.
  2. Converting the timestamp to a date by casting it.
SELECT cast(from_iso8601_timestamp('2021-03-19T00:00:00.000Z') AS date)

   _col0
------------
 2021-03-19
(1 row)
Martin Traverso
  • 4,731
  • 15
  • 24