Summary
- Expect more accurate but stricter datetime and timezone validation.
- Update format strings and timezone usage in custom SQL queries.
- ISO 8601 identifiers are preferred over abbreviations for stability and clarity.
For questions or migration help, please contact Dremio Support.
Overview
Dremio recently introduced stricter format enforcement and corrected timezone parsing behavior. These changes may break a small portion of previously valid queries related to datetime handling.
- The following functions will now enforce stricter parsing rules:
- TO_DATE
- TO_TIME
- TO_TIMESTAMP
- IS_DATE
- IS_TIME
- IS_TIMESTAMP
- UNIX_TIMESTAMP
- CONVERT_TIMEZONE
Changes include:
- "YY" now only accepts 2-digit years
- "YYYYYY" added for arbitrary-length years
- "HH"/"HH12" are equivalent and now require the AM/PM indicator
- Ambiguous patterns (e.g., "MON" vs. "MONTH", invalid day names) now return errors
Recommendation: Review any custom queries using the above mentioned functions and update the format strings to align with Dremio’s format documentation
2. Timezone Abbreviation Behavior (Corrected Semantics)
- Abbreviations like "PST" will now be rejected during periods when they aren’t in effect (e.g., summer — should use "PDT")
- Updated offsets for MSD, CLT, etc.
- Deprecated abbreviations: BRA, VOLT
Recommendation: Use ISO 8601-compliant identifiers (e.g., "America/Los_Angeles" or +08:00) as timezone abbreviations may be changed by the governing authority (https://www.iana.org/time-zones) at any point
Relevant Versions
Dremio Cloud -209/ Dremio Cloud -211
Examples
Example 1:
SELECT TO_DATE('2021-12-22', 'YY-MM-DD')
Previous behavior (No error):
2021-12-22
New behavior (Error):
Input text cannot be formatted to date
Corrected query:
SELECT TO_DATE('21-12-22', 'YY-MM-DD')
Example 2:
SELECT TO_TIME('09:15:00', 'HH:MI:SS')
Previous behavior (No Error):
09:15:00.000
New behavior (Error):
Ambiguous format. Please use HH24 or HH w/ AMPM
Corrected query (either of the following would work):
SELECT TO_TIME('09:15:00', 'HH24:MI:SS')
SELECT TO_TIME('09:15:00 AM', 'HH:MI:SS AMPM')
Example 3:
SELECT TO_TIMESTAMP('2021-07-31 01:02:03', 'YYYY-MM-DD HH:MI:SS')
Previous behavior (No Error):
2021-07-31 01:02:03.000
New behavior (Error):
Ambiguous format. Please use HH24 or HH w/ AMPM
Corrected query:
SELECT TO_TIMESTAMP('2021-07-31 01:02:03 AM', 'YYYY-MM-DD HH:MI:SS AMPM')
Example 4:
SELECT UNIX_TIMESTAMP('2021-12-22', 'YY-MM-DD')
Previous behavior (No error, even though the Year format does not match):
1640131200
New behavior (Throws an error):
Text '2021-12-22' could not be parsed at index 2
Corrected query:
SELECT UNIX_TIMESTAMP('21-12-22', 'YY-MM-DD')
Example 5:
SELECT
CONVERT_TIMEZONE('UTC', 'PDT', '2021-04-01 15:27:32'),
CONVERT_TIMEZONE('UTC', 'PST', '2021-04-01 15:27:32')
Previous behavior: (note that the "PST" one resolved to "-08:00", even though it doesn't make sense for date 2021-04-01, as it's in the DST range):
2021-04-01 08:27:32.000 2021-04-01 07:27:32.000
New behavior:
2021-04-01 08:27:32.000 2021-04-01 08:27:32.000