I have a query wherein one of the columns is a DATE type. I'm trying to convert that to the nanosecond representation of the timestamp associated with the date:

I can get a timestamp from a date by doing this:

SELECT TRY_TO_TIMESTAMP(TO_VARCHAR($1))
FROM (
    SELECT DATE($1) FROM VALUES ('2022-07-15'), ('2022-07-18'), ('2022-07-19'))

but using TRY_TO_NUMERIC doesn't work so I'm not sure what to do here.

CodePudding user response：

You can use the datediff function to return the nanoseconds since the start of the Unix epoch, which is 1970-01-01:

with source_data as
(
    select 
    COLUMN1::date as INPUT
    from (values
    ('2022-07-15'),
    ('2022-07-18'),
    ('2022-07-19')
    )
)
select   INPUT
        ,datediff(nanoseconds, '1970-01-01'::date, INPUT) as OUTPUT
from SOURCE_DATA
;