------------------------------------------------------------
-- Converting a datetime (year to second) value to UTC format 
--  Larry Kemmerling, CDI 2006-04
--
--   (Look at %DBA%\sql\proc\sp_dt2utc.sql for second example)
------------------------------------------------------------
CREATE PROCEDURE datetime2utc
      ( Datetime_in    datetime year to second )
        RETURNING      integer;
        DEFINE offSet  interval second( 9 ) to second;
        DEFINE UTC    interval second( 9 ) to second;
        DEFINE UTCC    char( 10 );
        DEFINE UTCI    integer;
        DEFINE epoch  datetime year to second;
-- set debug file to '/tmp/datetime2utc_trace';         
-- trace on;
-- When UTC time begins
        LET epoch = DATETIME(  1970 - 01 - 01   00 : 00 : 00  ) YEAR TO SECOND;   
-- Compute an offset that accounts for TZ
        LET offSet = ( SELECT CURRENT - ( epoch + DBINFO('utc_current') UNITS SECOND )                           
                       FROM SysTables WHERE TabId =  99  );
-- Compute the 'interval' value
        LET UTC = ( Datetime_in - epoch - offSet );
-- Convert the interval to char
        LET UTCC = UTC;
-- Convert the char to integer
        LET UTCI = UTCC;
-- NOTE: Discrepancies of 1-2 seconds may occur on busy systems.         
        RETURN UTCI;
END PROCEDURE;

{I haven't done any exhaustive testing but this procedure seems to
 produce correct results on my test system (Solaris 2.6, IDS 7.31.UC2).
 Also note that I intend to use this in situations where the datetime
 value passed in is not more than a few hours off of the current time.
 One problem I'm seeing is discrepancies of  1 - 2  seconds can occur on
 busy systems.

 I was hoping there was a dbinfo function to handle this but I haven't
 been able to find one.  Does such a function exist?  Is there a better
 way to do this?
}

---------------------------------------------------------
-- Convert DateTime to UnixTimeClock (UTC) (time_t)
-- Jonathan Leffler, CDI  2000-05
--    (Look at %DBA%\sql\proc\sp_datetime2utc.sql)
---------------------------------------------------------
-- dbinfo('UTC_TO_DATETIME',connected) to convert the
-- value in sysmaster:syssessions.connected to a datetime value 

CREATE PROCEDURE dt2utc(dt DATETIME YEAR TO SECOND) 
      RETURING INT;
    DEFINE i INTERVAL SECOND( 9 ) TO SECOND;
    DEFINE s CHAR( 10 );
    LET i = dt - DATETIME( 1970 - 01 - 01   00 : 00 : 00 ) YEAR TO SECOND;
    LET s = i;
    RETURN s;
END PROCEDURE;

--Yes, you do have to use a string in there.  The problem is that in
--2001 sometime, the Unix clock moves from 9 digits to 10 digits, and
--10 digits won't fit into an INTERVAL SECOND(9) TO SECOND variable.
--At that point, you have to do the reduction in two stages:
--1. Deal in whole days and multiple by 86400 (24*60*60).
--2. Deal in the hour to second part.
--More fiddly than difficult, but necessary for dates more than 30-odd
--years either side of 1970.

-- После 2001-09-09 01:46:39 (by Alex Gradinar)
        ,CASE WHEN sh_curtime >  999999999 
         THEN DATETIME( 1970 - 01 - 01   00 : 00 : 00 ) YEAR TO SECOND +  999999999  UNITS SECOND +
                (sh_curtime -  999999999 ) UNITS SECOND
         ELSE DATETIME(  1970 - 01 - 01   00 : 00 : 00  ) YEAR TO SECOND + sh_curtime UNITS SECOND
         END AS                         GMT_sysshmvals

---------------------------------------------------------
-- zest (SQL.RU 2005-04) 
---------------------------------------------------------
DROP FUNCTION Datetime2Second;
CREATE FUNCTION Datetime2Second (dt DATETIME YEAR TO SECOND)
        RETURNING INT;
        DEFINE checkpoint INT;
        DEFINE curr INTERVAL SECOND( 9 ) TO FRACTION; 
        DEFINE retval INT;
        LET checkpoint =  946684800 ;             
        LET curr = dt - EXTEND(DATE('1/1/2000'), YEAR TO FRACTION); 
        LET retval = CAST(CAST(curr AS VARCHAR( 64 )) AS INT);
        RETURN (retval +  946684800 ) ;
END FUNCTION;