daily pastebin goal
62%
SHARE
TWEET

ISO6801-MySQL

yregaieg Dec 20th, 2017 4 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- I made a Stored Function which can covert an ISO 8601 (2006-07-05T13:30:00+02:00) date to a UNIX TIMESTAMP of the
  2. -- corresponding UTC or GMT datetime, so you can compare timestamps from different timezones with eachother. Hope this can help someone.
  3.  
  4. CREATE FUNCTION ISO8601TOUNIXTIMESTAMP (iso varchar(25))
  5. RETURNS INTEGER(15)
  6. DETERMINISTIC
  7. BEGIN
  8. DECLARE CONVTIME INTEGER(11);
  9. SET CONVTIME = (SUBSTRING(iso,21,2) * 60) + SUBSTRING(iso,24,2);
  10. IF SUBSTRING(iso,20,1) = '+' THEN
  11. SET CONVTIME = 0 - CONVTIME;
  12. END IF;
  13. RETURN UNIX_TIMESTAMP(DATE_ADD(STR_TO_DATE(CONCAT(SUBSTRING(iso,1,10),' ',SUBSTRING(iso,12,8)),'%Y-%m-%d %H:%i:%s'), INTERVAL CONVTIME MINUTE));
  14. END
RAW Paste Data
Top