Presto/SQL - Converting string timestamp to date throws error

3
votes

NOTE: I am running my query in Qubole's presto and sql command engine.

I am trying to convert my string timestamp to just date but none of the options are working out.

My string timestamp looks like 2017-03-29 10:32:28.0 and I want to have it like 2017-03-29

I have tried following queries to convert this string timestamp to retrieve date

1. select cast(created as date) from table1

Value cannot be cast to date: 2017-05-26 17:23:58.0

2. select cast(from_iso8601_timestamp(created) as date) from table1

Invalid format: "2014-12-19 06:06:36.0" is malformed at " 06:06:36.0"

3. select date(created) from table1

Value cannot be cast to date: 2012-10-24 13:50:00.0

How I can convert this timestamp to date in presto/sql?

2
sqldatedatetimeselectpresto

2 Answers

3
votes

As far as explained in the documentation, prestoDB seems to expect timestamps in a format '2001-08-22 03:04:05.321', and dates in a '2001-08-22'.

One solution would be to use a string function to extract the relevant part of the string before converting it. We know that the date part is located before the first space in the string, so.

If you need the date part as a string datatype:

split_part(created, ' ', 1)

If you need the date part as a date datatype:

cast(split_part(created, ' ', 1) as date)
1
votes

You can try to use one of the following solutions:

  SELECT 
  '2017-03-29 10:32:28.0' AS input_string,
  DATE(date_parse('2017-03-29 10:32:28.0', '%Y-%m%-%d %H:%i:%s.%f')) AS solution_1,
  DATE(try_cast('2017-03-29 10:32:28.0' as timestamp)) AS solution_2