Determine Daylight Saving or Standard Time for Unix Timestamp

[kvang]

If given a unix timestamp, how do I determine which timezone to use (EST or EDT)? I'm trying to edit this function to automatically return the right time zone with the value passed.

CREATE OR REPLACE FUNCTION Unixts_To_Date(unixts IN PLS_INTEGER) RETURN DATE IS
        /**
         * Converts a UNIX timestamp into an Oracle DATE 
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date DATE;
        
        BEGIN
            
            IF unixts> max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too large for 32 bit limit'
                );
            ELSIF unixts <min_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too small for 32 bit limit' );
            ELSE
				oracle_date := unix_epoch + (unixts/86400);
            END IF;
            
            RETURN NEW_TIME(oracle_date, 'GMT', 'EDT');
        
END;
/

 

[SantaMufasa]

BKou,

How are you obtaining the Unix timestamp? (I know that when I issue the o/s command "date", it returns current date, time, and time zone.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[kvang]

I'm reading a time stamp in stored seconds from a field in an event log table. I want to find out if the date is during daylight saving time.

 

[kvang]

I could figure it out if I can find the dates when DST starts and ends. Any tip on that is helpful.

 

[SantaMufasa]

BKou,

In the U.S. (except for Hawaii and the parts of Arizona not under control of Indian Reservations), Daylight Savings Time currently:[ul][li]begins at 1:00 A.M. on the 2nd Sunday in March[/li][li]ends at 2:00 A.M. on the 1st Sunday in November.[/li][/ul]Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[kvang]

Actually, I was able to figure it out by interpreting someone's VB code. Here's what I came up with:

CREATE OR REPLACE FUNCTION Isdst(dDate IN DATE) RETURN INTEGER IS 

  dStartDST DATE;
  dEndDST DATE;
  DaysTilSun INTEGER;

BEGIN
   
   --* Figure out how many days after Mar 1, [Supplied Year] that the second Sunday occurs
   --  Then set when DST starts
  DaysTilSun := 15 - TO_CHAR(TO_DATE('03/01/' || TO_CHAR(dDate,'YYYY'), 'MM/DD/YYYY'), 'D');
  dStartDST := TO_DATE('03/01/' || TO_CHAR(dDate,'YYYY'), 'MM/DD/YYYY') + DaysTilSun;
  
   --* Figure out how many days before Nov 1, [Supplied Year] that Sunday occurs
   --  Then set when DST starts
   DaysTilSun := 8 - TO_CHAR(TO_DATE('11/01/' || TO_CHAR(dDate,'YYYY'), 'MM/DD/YYYY'), 'D');
   IF DaysTilSun = 7 THEN 
   	  DaysTilSun := 0; 
   END IF;
   dEndDST := TO_DATE('11/01/' || TO_CHAR(dDate,'YYYY'), 'MM/DD/YYYY') + DaysTilSun;
  
   --* Note that Anytime on the Sunday that DST starts is considered DST and
   --  ANYtime on the day it ends is considered DST
   IF dDate >= dStartDST AND dDate < dEndDST THEN
  	  RETURN 1;
   ELSE
  	  RETURN 0;
   END IF; 

END;
/