StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>These are some SQL UDFs, if you're not on TD13.10 you might just use the calculation instead:</p> <pre><code>/********** Converting Unix/POSIX time to a Timestamp Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 24 in 2011) Also working for negative numbers. The maximum range of Timestamps is based on the range of INTEGERs: 1901-12-13 20:45:52 (-2147483648) to 2038-01-19 03:14:07 (2147483647) Can be changed to use BIGINT instead of INTEGER 20101211 initial version - Dieter Noeth **********/ REPLACE FUNCTION UnixTime_to_TimeStamp (UnixTime INT) RETURNS TIMESTAMP(0) LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0)) + ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) ; SELECT UnixTime_to_TimeStamp(-2147483648) ,UnixTime_to_TimeStamp(0) ,UnixTime_to_TimeStamp(2147483647) ; /********** Converting a Timestamp to Unix/POSIX time Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 24 in 2011) The maximum range of Timestamps is based on the range of INTEGERs: 1901-12-13 20:45:52 (-2147483648) to 2038-01-19 03:14:07 (2147483647) Can be changed to use BIGINT instead of INTEGER 20101211 initial version - Dieter Noeth **********/ REPLACE FUNCTION TimeStamp_to_UnixTime (ts TIMESTAMP(6)) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN (CAST(ts AS DATE) - DATE '1970-01-01') * 86400 + (EXTRACT(HOUR FROM ts) * 3600) + (EXTRACT(MINUTE FROM ts) * 60) + (EXTRACT(SECOND FROM ts)) ; SELECT TimeStamp_to_UnixTime(TIMESTAMP '1901-12-13 20:45:52') ,TimeStamp_to_UnixTime(CURRENT_TIMESTAMP) ,TimeStamp_to_UnixTime(TIMESTAMP '2038-01-19 03:14:07') ; /********** Difference between two Timestamps in seconds 20101211 initial version - Dieter Noeth **********/ REPLACE FUNCTION TimeStamp_Diff_Seconds ( ts1 TIMESTAMP(6) ,ts2 TIMESTAMP(6) ) RETURNS DECIMAL(18,6) LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN (CAST((CAST(ts2 AS DATE AT 0)- CAST(ts1 AS DATE AT 0)) AS DECIMAL(18,6)) * 86400) + ((EXTRACT( HOUR FROM ts2) - EXTRACT( HOUR FROM ts1)) * 3600) + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60) + (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1)) ; SELECT TimeStamp_Diff_Seconds(TIMESTAMP '9999-12-31 23:59:59' ,TIMESTAMP '0001-01-01 00:00:00') ,TimeStamp_Diff_Seconds(CURRENT_TIMESTAMP ,TIMESTAMP '1970-01-01 00:00:00') ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload