Convert normal human readable datetime to unix timestamp in Snowflake

Question

I have created an SQL model in snowflake that gives a several outputs among those ones, I have two columns that are the next :

• The first column: called last_used that gives a human readable datetime in the next format 2023-12-01 20:24
• The second column called date that gives a human readable date in the next format : 2023-12-01

Is there any functionality in snowflake that converts those two columns into unix timestamp which will do the contrary of to_timestamp() ?

In a second hand, is there any possibility that unix timestamp in snowflake will be aware of timezone ?

Thank you in advance ! Any help is appreciated

Answer 1

as per the documentation for Date & Time Functions you want to use epoch_second and DATE_PART:

select 
    '2023-12-01 20:24'::timestamp as col_a
    ,'2023-12-01'::date as col_b
    ,date_part('epoch_second', col_a) as epoch_a
    ,date_part('epoch_second', col_b) as epoch_b
;

gives:

Unix timestamps do not in of themselves have a time zone, which is why in my past jobs the servers mostly use GMT to avoid timezone daylight savings changes messing with logs twice a year. But like all timestamps, you can convert from one timezone to another, via the CONVERT_TIMEZONE, I would just be explicit about the source/destination timezones, otherwise you will get the Snowflake servers, which are not GTM...