Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7371

DST/UTC cast/to_timestamp problem

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Labels:
      None

      Description

      With LC_TIME=fr_FR.UTF-8 and drillbits configured in UTC (like specified in http://www.openkb.info/2015/05/understanding-drills-timestamp-and.html#.VUzhotpVhHw find from https://drill.apache.org/docs/data-type-conversion/#to_timestamp)

      SELECT TIMEOFDAY();
      +-----------------------------+
      |           EXPR$0            |
      +-----------------------------+
      | 2019-09-11 08:20:12.247 UTC |
      +-----------------------------+
      

      Problems appears when cast/to_timestamp date (date related to the DST (Daylight Save Time) of some countries).

      To illustrate, all the next requests give the same wrong results:

      SELECT to_timestamp('2018-03-25 02:22:40 UTC','yyyy-MM-dd HH:mm:ss z');
      SELECT to_timestamp('2018-03-25 02:22:40','yyyy-MM-dd HH:mm:ss');
      SELECT cast('2018-03-25 02:22:40' as timestamp);
      SELECT cast('2018-03-25 02:22:40 +0000' as timestamp);
      +-----------------------+
      |        EXPR$0         |
      +-----------------------+
      | 2018-03-25 03:22:40.0 |
      +-----------------------+
      

      while the result should be "2018-03-25 02:22:40.0"

      An UTC date and time in string shouldn't change when casting to UTC timestamp.
      To illustrate, the next requests produce good results:

      SELECT to_timestamp('2018-03-26 02:22:40 UTC','yyyy-MM-dd HH:mm:ss z');
      +-----------------------+
      |        EXPR$0         |
      +-----------------------+
      | 2018-03-26 02:22:40.0 |
      +-----------------------+
      
      SELECT CAST('2018-03-24 02:22:40' AS timestamp);
      +-----------------------+
      |        EXPR$0         |
      +-----------------------+
      | 2018-03-24 02:22:40.0 |
      +-----------------------+
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              benj641 benj
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: