CODEWITHSUNDEEP
sqlintersystems-cache
eatonphil
eatonphil

Reputation: 13702

Cache-SQL ZDATETIME function in SQL?

What function can I use (similar to ZDATETIME() in ObjectScript) in Cache SQL to translate from a seconds from epoch timestamp to a UTC timestamp? I looked into the documentation and could only find information on doing this in Object Script... What function can you use in the SQL? Thanks!

Upvotes: 1

Views: 1040

Answers (2)

eatonphil
eatonphil

Reputation: 13702

Figured it out, I had to convert it all manually. I spent forever trying to find a pre-existing function, but here is how I did it:

select 
    TO_CHAR(substr(audit.stamp,1,5),'YYYY-MM-DD') AS Date,
    CASE LEN(CAST(substr(audit.stamp,7)/3600 AS INT))
    WHEN 2 THEN CAST(substr(audit.stamp,7)/3600 AS INT)
    ELSE '0' || CAST(substr(audit.stamp,7)/3600 AS INT)
    END || ':' ||
    CASE LEN(CAST((substr(audit.stamp,7)/60)#60 AS INT))
    WHEN 2 THEN CAST((substr(audit.stamp,7)/60)#60 AS INT)
    ELSE '0' || CAST((substr(audit.stamp,7)/60)#60 AS INT)
    END || ':' ||
    CASE LEN(CAST(substr(audit.stamp,7)#60 AS INT))
    WHEN 2 THEN CAST(substr(audit.stamp,7)#60 AS INT)
    ELSE '0' || CAST(substr(audit.stamp,7)#60 AS INT)
    END AS Time
from utility.audit

As taken from the Cache SQL Reference Manual:

CLOCKTIME    
  NEW
  SET Time=$PIECE($HOROLOG,",",2)
  SET Sec=Time#60
  SET Totmin=Time\60
  SET Min=Totmin#60
  SET Milhour=Totmin\60
  IF Milhour=12 { SET Hour=12,Meridian=" pm" }
  ELSEIF Milhour>12 { SET Hour=Milhour-12,Meridian=" pm" }
  ELSE { SET Hour=Milhour,Meridian=" am" }
  WRITE !,Hour,":",Min,":",Sec,Meridian
  QUITCLOCKTIME    
  NEW
  SET Time=$PIECE($HOROLOG,",",2)
  SET Sec=Time#60
  SET Totmin=Time\60
  SET Min=Totmin#60
  SET Milhour=Totmin\60
  IF Milhour=12 { SET Hour=12,Meridian=" pm" }
  ELSEIF Milhour>12 { SET Hour=Milhour-12,Meridian=" pm" }
  ELSE { SET Hour=Milhour,Meridian=" am" }
  WRITE !,Hour,":",Min,":",Sec,Meridian
  QUIT

Thanks!

Upvotes: 0

William
William

Reputation: 6610

I think you're looking for something like this;

DECLARE @epoch INT
SET @epoch = 1348519792
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), DATEADD(s, @epoch, '19700101 00:00:00:000'))

SQL Fiddle

Upvotes: 2

Related Questions