2 Replies Latest reply on Nov 17, 2014 1:30 AM by 2758586

    Daylight Savings Time

    2758586

      Hello everyone, I have two databases with the data stored from the same source. One database stores the time as GMT and the other stores the time as local time (MST).            MST = GMT - 7 (or -6 depending the DST). My task is to compare the values in the two databases for the time field. I converted the GMT time to local time using the New_TIME function as below:

       

      Select NEW_TIME(TO_DATE('05-10-2010 05:00', 'MM-DD-YYYY HH24:MI), 'GMT', 'MST') "DATETIME" FROM DUAL;

       

      The problem that I am facing is that  due to the daylight savings time, I am getting about 1 hour difference in some of my records. I thought of changing MST to MDT in the above statement but that will again cause the same problem with other half of the records. So, can anyone please help me how to consider the daylight savings time in datetime conversion?

       

      I have only the Read permission on the table. So I cannot change the data type. Thank you for your help and time.

        • 1. Re: Daylight Savings Time

          Wrong forum!

           

          This forum, as the title says, is ONLY for sql developer questions and NOT for sql or PL/SQL questions.

           

          Please mark this thread ANSWERED and repost in the SQL and PL/SQL forum.

          SQL & PL/SQL

           

          I have two databases with the data stored from the same source. One database stores the time as GMT and the other stores the time as local time (MST).            MST = GMT - 7 (or -6 depending the DST). My task is to compare the values in the two databases for the time field. I converted the GMT time to local time using the New_TIME function as below:

           

          Select NEW_TIME(TO_DATE('05-10-2010 05:00', 'MM-DD-YYYY HH24:MI), 'GMT', 'MST') "DATETIME" FROM DUAL;

          Well using the NEW_TIME function is the problem. Did you RTFM for that function?

          https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2036.htm

          See Table 8-1 - It shows you, as your own example also shows, that the timezones you can specify INCLUDE a specific indicator for each standard or daylight time. That limits the usefulness of that function.

           

          Use the FROM_TZ function - it doesn't have that limitation.

          https://docs.oracle.com/cd/B28359_01/server.111/b28298/ch4datetime.htm#i1007699

           

          That doc section shows how to use the AT TIME ZONE clause to convert from one time zone to another.

           

          For your use case you should just convert to GMT and then compare directly.

           

          If you need any additional help repost the problem in the appropriate forum.

          1 person found this helpful
          • 2. Re: Daylight Savings Time
            2758586

            Thanks for the reply. I have moved my question to the correct forum.