Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Function

DATETRUNC

Action


Excerpt
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


Code Block
languageperl
// Input is 1492761464 == 21 April 2017 07:57:44 GMTUTC
@x = SELECT 1492761464 AS Input;

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

// Returns 1492758000 == 21 April 2017 07:00:00 GMTUTC
SELECT DATETRUNC(@x.Input, "hour") AS Truncated FROM @x;
The following is an example of truncating 18-August-2017 19:18:03:


Code Block
languageperl
@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;


truncationISO-8601 result
"M" minute2017-08-18T19:18:00Z
"H" hour2017-08-18T19:00:00Z
"d" day2017-08-18T00:00:00Z
"m" month2017-08-01T00:00:00Z
"y" year2017-01-01T00:00:00Z

For further examples, see datetime handling .

Notes

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