I have a SQL table containing timestamps of the following format:

2022-02-07 12:57:45.000

In SQL Server, you can convert this to a floating point date serial number (days since 1900-01-01):

select convert(float, my_timestamp_field) as float_serial_number

which yields an output of:

I am trying to get the same output in Snowflake, but cant figure out how to return a float. This is as close as I have gotten:

select 
datediff(day, '1900-01-01', '2022-02-07 12:57:45.000') as integer_only,
timestampdiff(day, '1900-01-01', '2022-02-07 12:57:45.000') as same_as_above

which yields an output of:

The output I need in Snowflake (this takes the time into account where 12pm = 0.5):

CodePudding user response：

Convert is a reserved keyword in Snowflake, so here's a UDF named "convert_ts" you can use. You can of course also use the SQL, but it's cleaner as a UDF:

create or replace function convert_ts(ts TIMESTAMP)
returns float
language sql
as
$$
    (datediff(day, '1900-01-01', TS)::float   
    (datediff(nanoseconds, '1900-01-01', TS)::float - 
    datediff(nanoseconds, '1900-01-01', date_trunc(day, TS)))::float / 86400000000000::float)
$$;

select convert_ts('2022-02-07 12:57:45.00000000');

CodePudding user response：

You could calculate the diff in decimals using either these- (seconds, milliseconds, nanoseconds) in datediff and dividing that by the appropriate denominator

set mydate='2022-02-07 12:57:45.000'::timestamp;

select datediff(seconds, '1900-01-01', $mydate)::float/86400