v.24.12New Feature

Add toUnixTimestamp64Second Function for DateTime64 Conversion to Int64

Official CH changelog text
Add function toUnixTimestamp64Second which converts a DateTime64 to a Int64 value with fixed second precision, so we can support return negative value if date is before the unix epoch. #70597 (zhanglistar). #73146 (Robert Schulze).
Added the toUnixTimestamp64Second function to convert DateTime64 values to Int64 UNIX timestamps with second precision, supporting negative values for dates before the Unix epoch.

Why it matters

This feature enables precise conversion of high-precision DateTime64 values to standard UNIX timestamp representation in seconds, including dates prior to January 1, 1970. This expands compatibility and correctness when working with historical or negative-time data.

How to use it

Use the function by passing a DateTime64 value as its argument, for example:

SELECT toUnixTimestamp64Second(your_datetime64_column) FROM your_table;

This will return the equivalent Int64 UNIX timestamp value in seconds, preserving negative values for pre-epoch dates.

Related resources

PreviousNext