4 Replies Latest reply on Dec 12, 2013 8:11 AM by Sylvain YVON

    Unix Epoch date conversion in Oracle, taking into account the local Time Zone and Daylight Savings

      Hi

       

      I'm struggling to convert correctly a Unix Epoch Date into a correct local date representation, taking into account the local Time Zone and Daylight Savings (for Belgium in my example)

       

      While browsing throught the different topics on this issue, I already came up with the follwowing command:

      Select to_char(cast(to_date('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')+<Epoch Date to convert>/86400 as timestamp with local time zone),'DD/MM/YYYY HH24:MI:SS') from dual;

       

      However, this command does not convert correctly the dates.

      Some examples:

      BMC user tool date representation :     8/02/2013 18:43:54

      Epoch Date in Oracle table : 1360345434

      Date conversion result: 08/02/2013 17:43:54

      Difference: 1h

       

      BMC user tool date representation :     5/06/2013 16:39:40

      Epoch Date in Oracle table : 1370443180

      Date conversion result: 05/06/2013 14:39:40

      Difference: 2h

       

       

      BMC user tool date representation :     9/11/2013 11:15:31

      Epoch Date in Oracle table : 1383992131

      Date conversion result: 09/11/2013 10:15:31

      Difference: 1h

       

      When looking at the table below, the difference can be explained by the fact that neither the local timezone, nor the daylight savings or taken into account.

       

      Sunday, 31 March 20131:59:59NoUTC+1hCET
      02:00:00 → 03:00:00+1hUTC+2hCESTDST starts
      Sunday, 27 October 20132:59:59+1hUTC+2hCEST
      03:00:00 → 02:00:00NoUTC+1hCETDST ends

       

      Can anybody help me in how to convert Unix Epoch Dates correctly so that the local time zone and daylight savings are taken into account?

       

      Thanks!

       

      Best Regards

      Werner

        • 1. Re: Unix Epoch date conversion in Oracle, taking into account the local Time Zone and Daylight Savings
          Mark Walters

          Looks like you have several options on the Oracle side - have a look at Convert UNIX Epoch Date to Oracle Display Value.  Google returns plenty of other examples too.

           

          Mark

          • 2. Re: Unix Epoch date conversion in Oracle, taking into account the local Time Zone and Daylight Savings
            Julian Medina

            Hi,

             

            I have got a oracle function for this, the only disadvantage is to update it every year with the date of the day of time change.

             

            create or replace

            function Format_Date(EPOCH_TO_CONVERT IN NUMBER) return varchar2 is

              RESULT varchar(19);

            BEGIN

              CASE

              --Summer 2006

              WHEN (EPOCH_TO_CONVERT> 1143334800 AND EPOCH_TO_CONVERT< 1162080000) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

              --Summer 2007

              WHEN (FECHA > 1174784400 AND EPOCH_TO_CONVERT< 1193536800) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

                --Summer 2008

                WHEN (EPOCH_TO_CONVERT> 1206838800 AND FECHA < 1224986400) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + FECHA/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

                --Summer 2009

                WHEN (EPOCH_TO_CONVERT> 1238288400 AND EPOCH_TO_CONVERT< 1256436000) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

                --Summer 2010

                WHEN (FECHA > 1269738000 AND EPOCH_TO_CONVERT< 1288490400) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

                --Summer 2011

                WHEN (EPOCH_TO_CONVERT> 1301191200 AND FECHA < 1319943600) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + FECHA/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

                --Summer 2012

                WHEN (EPOCH_TO_CONVERT> 1332640800 AND EPOCH_TO_CONVERT< 1351393200) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

                --Summer 2013

                WHEN (FECHA > 1364608800 AND FECHA < 1382842800) THEN RESULT:=TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + FECHA/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

               

               ELSE RESULT:=TO_CHAR(TO_DATE('01/01/1970 01:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS');

              END CASE;

              return(RESULT);

            END Format_Date;

             

            Hoping to be useful

            Julián Medina

            1 of 1 people found this helpful
            • 3. Re: Unix Epoch date conversion in Oracle, taking into account the local Time Zone and Daylight Savings

              Hi Julián

               

              Thanks for showing me the correct path to take.

              I took a look into your solution, but found some errors in it (at least from my perspective, perhaps it has to do with other requirements you have)

              When I convert the epoch values you use with a conversion tool (Epoch Converter - Unix Timestamp Converter), they are not always correct.

               

              So I updated and expanded you script so it will last until the end of supported dates.

              I also update it to return a timestamp.

               

              Here's the new script:

              create or replace

              function ConvertEpochToTimestamp(EPOCH_TO_CONVERT IN NUMBER) return TIMESTAMP is

                RESULT TIMESTAMP;

                SUMMER INTEGER;

              BEGIN

                CASE

                  --Summer 2006 > 26/03/2006 01:59:59 and < 29/10/2006 03:00:00

                  WHEN (EPOCH_TO_CONVERT> 1143334799 AND EPOCH_TO_CONVERT< 1162087200) THEN SUMMER:=1;

                  --Summer 2007 > 25/03/2007 01:59:59 AND < 28/10/2007 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1174784399 AND EPOCH_TO_CONVERT< 1193536800) THEN SUMMER:=1;

                  --Summer 2008 > 30/03/2008 01:59:59 AND < 26/10/2008 03:00:00

                  WHEN (EPOCH_TO_CONVERT> 1206838799 AND EPOCH_TO_CONVERT < 1224986400) THEN SUMMER:=1;

                  --Summer 2009 > 29/03/2009 01:59:59 AND < 25/10/2009 03:00:00

                  WHEN (EPOCH_TO_CONVERT> 1238288399 AND EPOCH_TO_CONVERT< 1256436000) THEN SUMMER:=1;

                  --Summer 2010 > 28/03/2010 01:59:59 AND < 31/10/2010 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1269737999 AND EPOCH_TO_CONVERT< 1288490400) THEN SUMMER:=1;

                  --Summer 2011 > 27/03/2011 01:59:59 AND < 30/10/2011 03:00:00

                  WHEN (EPOCH_TO_CONVERT> 1301187599 AND EPOCH_TO_CONVERT < 1319940000) THEN SUMMER:=1;

                  --Summer 2012 > 25/03/2012 01:59:59 AND < 28/10/2012 03:00:00

                  WHEN (EPOCH_TO_CONVERT> 1332637199 AND EPOCH_TO_CONVERT< 1351389600) THEN SUMMER:=1;

                  --Summer 2013 > 31/03/2013 01:59:59 AND < 27/10/2013 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1364691599 AND EPOCH_TO_CONVERT < 1382839200) THEN SUMMER:=1;

                  --Summer 2014 > 30/03/2014 01:59:59 AND < 26/10/2014 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1396141199 AND EPOCH_TO_CONVERT < 1414288800) THEN SUMMER:=1;

                  --Summer 2015 > 29/03/2015 01:59:59 AND < 25/10/2015 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1427590799 AND EPOCH_TO_CONVERT < 1445738400) THEN SUMMER:=1;

                  --Summer 2016 > 27/03/2016 01:59:59 AND < 30/10/2016 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1459040399 AND EPOCH_TO_CONVERT < 1477792800) THEN SUMMER:=1;       

                  --Summer 2017 > 26/03/2017 01:59:59 AND < 29/10/2017 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1490489999 AND EPOCH_TO_CONVERT < 1509242400) THEN SUMMER:=1;

                  --Summer 2018 > 25/03/2018 01:59:59 AND < 28/10/2018 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1521939599 AND EPOCH_TO_CONVERT < 1540692000) THEN SUMMER:=1;

                  --Summer 2013 > 31/3/2019 01:59:59 AND < 27/10/2019 03:00:00

                  WHEN (EPOCH_TO_CONVERT > 1553993999 AND EPOCH_TO_CONVERT < 1572141600) THEN SUMMER:=1;

                  --Summer 2020 > 29/03/2020 1:59:59 and < 25/10/2020 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 1585443599 AND EPOCH_TO_CONVERT< 1603591200) THEN SUMMER:=1;

                  --Summer 2021 > 28/03/2021 1:59:59 AND < 31/10/2021 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1616893199 AND EPOCH_TO_CONVERT< 1635645600) THEN SUMMER:=1;

                  --Summer 2022 > 27/03/2022 1:59:59 AND < 30/10/2022 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 1648342799 AND EPOCH_TO_CONVERT < 1667095200) THEN SUMMER:=1;

                  --Summer 2023 > 26/03/2023 1:59:59 AND < 29/10/2023 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 1679792399 AND EPOCH_TO_CONVERT< 1698544800) THEN SUMMER:=1;

                  --Summer 2024 > 31/03/2024 1:59:59 AND < 27/10/2024 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1711846799 AND EPOCH_TO_CONVERT< 1729994400) THEN SUMMER:=1;

                  --Summer 2025 > 30/03/2025 1:59:59 AND < 26/10/2025 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 1743296399 AND EPOCH_TO_CONVERT < 1761444000) THEN SUMMER:=1;

                  --Summer 2026 > 29/03/2026 1:59:59 AND < 25/10/2026 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 1774745999 AND EPOCH_TO_CONVERT< 1792893600) THEN SUMMER:=1;

                  --Summer 2027 > 28/03/2027 1:59:59 AND < 31/10/2027 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1806195599 AND EPOCH_TO_CONVERT < 1824948000) THEN SUMMER:=1;

                  --Summer 2028 > 26/03/2028 1:59:59 AND < 29/10/2028 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1837645199 AND EPOCH_TO_CONVERT < 1856397600) THEN SUMMER:=1;   

                  --Summer 2029 > 25/03/2029 1:59:59 AND < 28/10/2029 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1869094799 AND EPOCH_TO_CONVERT < 1887847200) THEN SUMMER:=1;

                  --Summer 2030 > 31/03/2030 1:59:59 AND < 27/10/2030 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1901149199 AND EPOCH_TO_CONVERT < 1919296800) THEN SUMMER:=1;       

                  --Summer 2031 > 30/03/2031 1:59:59 AND < 26/10/2031 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1932598799 AND EPOCH_TO_CONVERT < 1950746400) THEN SUMMER:=1;

                  --Summer 2032 > 28/03/2032 1:59:59 AND < 31/10/2032 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1964048399 AND EPOCH_TO_CONVERT < 1982800800) THEN SUMMER:=1;

                  --Summer 2033 > 27/03/2033 1:59:59 AND < 30/10/2033 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 1995497999 AND EPOCH_TO_CONVERT < 2014250400) THEN SUMMER:=1;   

                  --Summer 2034 > 26/03/2034 1:59:59 and < 29/10/2034 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 2026947599 AND EPOCH_TO_CONVERT< 2045700000) THEN SUMMER:=1;

                  --Summer 2035 > 25/03/2035 1:59:59 AND < 28/10/2035 3:00:00

                  WHEN (EPOCH_TO_CONVERT > 2058397199 AND EPOCH_TO_CONVERT< 2077149600) THEN SUMMER:=1;

                  --Summer 2036 > 30/03/2036 1:59:59 AND < 26/10/2036 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 2090451599 AND EPOCH_TO_CONVERT < 2108599200) THEN SUMMER:=1;

                  --Summer 2037 > 29/03/2037 1:59:59 AND < 25/10/2037 3:00:00

                  WHEN (EPOCH_TO_CONVERT> 2121901199 AND EPOCH_TO_CONVERT < 2140048800) THEN SUMMER:=1;

                  ELSE SUMMER:=0;

                END CASE;

                IF SUMMER = 1 THEN

                  RESULT := TO_TIMESTAMP(TO_CHAR(TO_DATE('01/01/1970 02:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');

                ELSE

                  RESULT := TO_TIMESTAMP(TO_CHAR(TO_DATE('01/01/1970 01:00:00', 'DD/MM/YYYY HH24:MI:SS') + EPOCH_TO_CONVERT/(60*60*24),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS'); 

                END IF;

                return(RESULT);

              END ConvertEpochToTimestamp;

               

              Best Regards

              Werner

              • 4. Re: Unix Epoch date conversion in Oracle, taking into account the local Time Zone and Daylight Savings
                Sylvain YVON

                Hi guys,

                Maybe you could make it more easily maintainable by using a configuration table to store the from/to dates ?