Skip to main content

1E 23.11 (SaaS)

DATETRUNC

Function

DATETRUNC

Action

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

Parameters

(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:

    • "year" or "y"

    • "month" or "m"

    • "day" or "d"

    • "hour" or "H"

    • "minute" or "M"

Return values

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

Example

// 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;

truncation

ISO-8601 result

"M" minute

2017-08-18T19:18:00Z

"H" hour

2017-08-18T19:00:00Z

"d" day

2017-08-18T00:00:00Z

"m" month

2017-08-01T00:00:00Z

"y" year

2017-01-01T00:00:00Z

For further examples, seedatetime handling.

Notes

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