PostgreSQL | TO_DATE() / TO_TIMESTAMP() gotchas
A recent thread on -general sparked my interest as I'm working with a large and heterogeneous code base recently converted from Oracle which makes heavy use of TO_DATE()
, and it seems this function has a couple of gotchas worthy of consideration.
1. TO_DATE() does not really validate dates.
Even if passed an invalid date, it will happily munge it into a nominally valid date - which is probably not what you want:
# SELECT to_date('2014/02/31', 'YYYY/MM/DD'); to_date ------------ 2014-03-03 (1 row)
A possible workaround is to explicitly cast the string literal to DATE, e.g.:
# SELECT '2014/02/31'::DATE; ERROR: date/time field value out of range: "2014/02/31" LINE 1: SELECT '2014/02/31'::DATE; ^ # SELECT '2014/02/28'::DATE; date ------------ 2014-02-28 (1 row)
but this won't work with as wide a range of date formats as TO_DATE().
If you really need to parse arbitrary date strings, a function like this might make things safer by converting the output date to the original format, comparing input and output strings and raising an error if the value fails a round-trip:
CREATE OR REPLACE FUNCTION to_date_safe(p_date TEXT, p_format TEXT, OUT r_date DATE) LANGUAGE plpgsql AS $$ BEGIN r_date = TO_DATE(p_date, p_format); IF TO_CHAR(r_date, p_format) != p_date THEN RAISE EXCEPTION 'Input date % does not match output date %', p_date, r_date; END IF; END; $$;
This will mean a slight performance hit, and there might be side-effects I haven't thought about yet, but it seems preferable to silently accepting an incorrect date.
# SELECT to_date_safe('2014/02/31', 'YYYY/MM/DD'); ERROR: Input date 2014/02/31 does not match output date 2014-03-03
2. PostgreSQL's TO_DATE() is not Oracle's TO_DATE()
In Oracle, the DATE datatype is actually equivalent to PostgreSQL's TIMESTAMP(0) - meaning Oracle's TO_DATE() is actually working with timestamps - whereas PostgreSQL's TO_DATE() is working with dates only. Crucially, PostgreSQL's TO_DATE() will ignore any time specifications, meaning the following comparison will not return the result you might be expecting if familiar with the Oracle version:
# SELECT TO_DATE('2014-01-26 10:00:00', 'YYYY-MM-DD HH24:MI:SS') < TO_DATE('2014-01-26 22:00:00', 'YYYY-MM-DD HH24:MI:SS'); ?column? ---------- f
If you are converting TO_DATE() to PostgreSQL, use TO_TIMESTAMP() unless you are comparing calendar dates only. However TO_TIMESTAMP() suffers from the same issue as TO_DATE().
# SELECT TO_TIMESTAMP('2014-02-31 25:99:00', 'YYYY-MM-DD HH24:MI:SS'); TO_TIMESTAMP ------------------------ 2014-03-04 02:39:00+09 (1 row)
Again, a simple wrapper function doing reverse verification should provide some protection:
CREATE OR REPLACE FUNCTION to_timestamp_safe(p_date TEXT, p_format TEXT, OUT r_date TIMESTAMP) LANGUAGE plpgsql AS $$ BEGIN r_date = TO_TIMESTAMP(p_date, p_format); IF TO_CHAR(r_date, p_format) != p_date THEN RAISE EXCEPTION 'Input date % does not match output date %', p_date, r_date; END IF; END; $$;
Not exhaustively tested but seems to work:
# SELECT to_timestamp_safe('2014/02/31 10:00:00', 'YYYY/MM/DD HH24:MI:SS'); ERROR: Input date 2014/02/31 10:00:00 does not match output date 2014-03-03 10:00:00 # SELECT to_timestamp_safe('2014/02/28 10:00:00', 'YYYY/MM/DD HH24:MI:SS'); to_timestamp_safe --------------------- 2014-02-28 10:00:00 (1 row)
See also this page for some background on why this issue is still around