Snowflake SQL API date format

Question

I am now working on loading some data from Snowflake using the REST API called SQL API. The issue is that Snowflake uses some weird format for fields with DATE type when creating the response JSON.

I have this example field metadata:

{
  "name" : "...",
  "database" : "...",
  "schema" : "...",
  "table" : "...",
  "type" : "date",
  "scale" : null,
  "precision" : null,
  "length" : null,
  "collation" : null,
  "nullable" : true,
  "byteLength" : null
}

And in the resultset its value is "9245". Using the query in the browser I see that the actual value is 1995-04-25.

What magic function decodes this integer back to a date?

Answer 1

Based on documentation Getting the Data From the Results

DATE

Integer value (in a string) of the number of days since the epoch (e.g. 18262).

Related: Why is 1/1/1970 the “epoch time”?

Check:

SELECT DATEADD(day, 9245, '1970-01-01'::DATE)
--1995-04-25   

SELECT '1970-01-01'::DATE + INTERVAL '9245 DAYS';
-- 1995-04-25

db<>fiddle demo