Tuesday, June 07, 2005

Oracle PL/SQL function to get current time in UTC

This snippet is an Oracle PL/SQL function that returns the current timestamp in the UTC time zone. It takes advantage of the EXTRACT Oracle built in returning its result in UTC.

FUNCTION getUTCDate RETURN DATE
AS
  utcYear NUMBER(4);
  utcMonth NUMBER(2);
  utcDay NUMBER(2);
  utcHour NUMBER(2);
  utcMinute NUMBER(2);
  utcSecond NUMBER(2);
  dateString VARCHAR2(30);
  ts TIMESTAMP WITH TIME ZONE;
BEGIN
  ts := SYSTIMESTAMP;
  utcYear := EXTRACT(YEAR FROM ts);
  utcMonth := EXTRACT(MONTH FROM ts);
  utcDay := EXTRACT(DAY FROM ts);
  utcHour := EXTRACT(HOUR FROM ts);
  utcMinute := EXTRACT(MINUTE FROM ts);
  utcSecond := TRUNC(EXTRACT(SECOND FROM ts));
  --
  dateString := TO_CHAR(utcYear) || ':' ||
                TO_CHAR(utcMonth) || ':' ||
                TO_CHAR(utcDay) || ':' ||
                TO_CHAR(utcHour) || ':' ||
                TO_CHAR(utcMinute) || ':' ||
                TO_CHAR(utcSecond);
  RETURN TO_DATE(dateString, 'yyyy:mm:dd:hh24:mi:ss');
END getUTCDate;

No comments: