Oracle date to unix timestamp

My Oracle date to unix timestamp (oracle date to unix date, CET, CEST) solution. Daylight saving time (Central European Time - CET vs. Central European Summer Time - CEST) friendly.

Solution - f_date_to_unixts

 FUNCTION f_date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /* Converts an Oracle DATE to a UNIX timestamp */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;
        tzd VARCHAR2(4);
 
        BEGIN
            IF oracle_date IS NULL THEN 
                RETURN (NULL);
            END IF;
            IF oracle_date > max_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
            ELSIF oracle_date < min_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
            ELSE
                unix_ts := (oracle_date - unix_epoch) / (1/86400);
                tzd := TO_CHAR( FROM_TZ( CAST( oracle_date AS  TIMESTAMP), 'CET'), 'TZD' );
                IF tzd = 'CET' THEN
                  unix_ts := unix_ts - 3600;
                ELSIF tzd = 'CEST' THEN
                  unix_ts := unix_ts - 7200;
                ELSE
                  RAISE_APPLICATION_ERROR( -20902,'Unknown TZD "' || tzd || '"' );
                END IF;
            END IF;
 
            RETURN (unix_ts);
  END f_date_to_unixts;

Tests

Compare php-strtotime and oracle-f_date_to_unixts

  SELECT 
    st01.pkg_funkce.f_date_to_unixts( dt_a ) AS db_ts_a,
    ts_a,
    st01.pkg_funkce.f_date_to_unixts( dt_b ) AS db_ts_b,
    ts_b
  FROM 
  (
    SELECT 
    to_date('01/15/2006 10:02:03', 'MM/DD/YYYY HH24:MI:SS') AS dt_a, 
    to_date('05/15/2006 10:02:03', 'MM/DD/YYYY HH24:MI:SS') AS dt_b,
    1137315723 AS ts_a, -- from php with strtotime('01/15/2006 10:02:03')
    1147680123 AS ts_b  -- from php with strtotime('05/15/2006 10:02:03')
    FROM dual
  )

  	DB_TS_A	         TS_A	         DB_TS_B	TS_B
 1	1137315723	1137315723	1147680123	1147680123

date_to_unixts(null)

  SELECT st01.pkg_funkce.f_date_to_unixts(datum) FROM ( SELECT NULL AS datum FROM dual )

 return null

Modify

Timezone names

 SELECT * FROM v$timezone_names WHERE tzname LIKE '%Prague'

       TZNAME	TZABBREV
 1	Europe/Prague	LMT
 2	Europe/Prague	PMT
 3	Europe/Prague	CET
 4	Europe/Prague	CEST

Test modifications

 SELECT 
  to_char( from_tz( CAST( dt_a AS  TIMESTAMP), 'CET'), 'TZD' ),
  to_char( from_tz( CAST( dt_b AS  TIMESTAMP), 'CET'), 'TZD' ),
  decode ( 
     to_char( from_tz( CAST( dt_a AS  TIMESTAMP), 'CET'), 'TZD' ),
    'CET', 3600, 'CEST', '7200', 0 
  ),
  decode ( 
    to_char( from_tz( CAST( dt_b AS  TIMESTAMP), 'CET'), 'TZD' ),
    'CET', 3600, 'CEST', '7200', 0 
  )
  FROM 
  ( 
    SELECT 
    to_date('01/15/2006 10:02:03', 'MM/DD/YYYY HH24:MI:SS') AS dt_a, 
    to_date('05/15/2006 10:02:03', 'MM/DD/YYYY HH24:MI:SS') AS dt_b
    FROM dual
  )

 1	CET	CEST	3600	7200  

SELECT SYSTIMESTAMP FROM dual;

  	SYSTIMESTAMP
 1	09.03.06 13:24:08,136078 +01:00

SELECT dbtimezone,sessiontimezone FROM dual;

   	DBTIMEZONE	SESSIONTIMEZONE
 1	+01:00	+01:00

SELECT
  round( ( CAST( SYS_EXTRACT_UTC( TIMESTAMP '2006-05-15 10:02:03.00 CET'  ) AS DATE )
   - TO_DATE('19700101', 'YYYYMMDD')) * 86400 ) - 1147680123 AS err_b 
  , round( ( CAST( SYS_EXTRACT_UTC(TIMESTAMP '2006-01-15 10:02:03.00 CET' ) AS DATE )
   - TO_DATE('19700101', 'YYYYMMDD')) * 86400 ) - 1137315723 AS err_a
  FROM dual