Search code examples
prestotrino

Presto sql function date_parse fails for specific date (1960-01-01)

How to resolve this presto sql error for date_parse('1960-01-01', '%Y-%m-%d')

enter image description here

This function works fine for other dates.

Solution

  • This is due to a long-standing issue with how Presto models timestamps. Long story short, the implementation of timestamps is not compliant with the SQL specification and it incorrectly attempts to treat them as "point in time" or "instant" values and interpret them within a time zone specification. For some dates and time zone rules, the values are undefined due to daylight savings transitions, etc.

    This was fixed in recent versions of Trino (formerly known as Presto SQL), so you may want to update.

    By the way, you can convert a varchar to a date using the date() function or by casting the value to date:

    trino> select date('1960-01-01');
   _col0
------------
 1960-01-01
(1 row)

trino> select cast('1960-01-01' as date);
   _col0
------------
 1960-01-01
(1 row)