1E 23.11 (SaaS)

Truncates a date-time value, expressed as a Unix epoch time integer, to a given resolution and returns a corresponding Unix epoch time integer.

(integer) Unix epoch time integer, representing a datetime value

(string) Resolution - Extent to which the date time should be truncated, which can be one of:

(integer) Truncated date time value expressed as a Unix epoch time.

// Input is 1492761464 == 21 April 2017 07:57:44 UTC
@x = SELECT 1492761464 AS Input;

// Returns 1483228800 == 01 January 2017 00:00:00 UTC
SELECT DATETRUNC(@x.Input, "year") AS Truncated FROM @x;

// Returns 1492758000 == 21 April 2017 07:00:00 UTC
SELECT DATETRUNC(@x.Input, "hour") AS Truncated FROM @x;

The following is an example of truncating 18-August-2017 19:18:03:

@datetime = SELECT "2017-08-18T19:18:03Z" AS string;
// convert date string to unixepoch integer, truncate, then convert result back to ISO-8601 formatted string
SELECT EPOCHTOJSON( DATETRUNC( STRFTIME("%s",string), "H") ) AS TS FROM @datetime;

For further examples, seedatetime handling.

Note that this function performs truncation, as opposed to rounding.