Recently, i was working on a requirement, where i need to convert the Timestamp into UNIX epoch format.
Quick Explanation about UNIX epoch : Unix time is a numeric value, which calculates the no of seconds that has been passed since 01st January 1970 to current timestamp. And that value is called UNIX TIME.
So the Date Part Function, Solves the current problem:
DATE_PART( date_or_time_part , date_or_time_expr )
returns NUMBER
Point to remember:
Note : date_or_time_expr can be a date, time, or timestamp.
a) Wrong way of doing it ?
SELECT
DATE_PART( EPOCH, '2021-08-03 11:46:42.245' ) AS UNIX_VAL;
Immediately after you execute the above statement the compiler will scold you saying that .....
SQL compilation error: Function EXTRACT does not support VARCHAR(23) argument type
Reason: Because the data in the function, is in string format, so the function couldn't figure out the date from the string as it can only accept TIME, DATE, TIMESTAMP.
b) Correct Way :
SELECT
DATE_PART( EPOCH, TO_TIMESTAMP_NTZ('2021-08-03 11:46:42.245')) AS UNIX_VAL;
1627991202
I hope you found this example intuitive and insightful, and hope it helps someone in the data community.