DATETRUNC

    Syntax

    DATETRUNC( datetime, unit [ , number ] )
    

    Description

    Rounds datetime down to the given unit. If optional number is given, then the value is rounded down to a number multiple of unit (omitting number is the same as number = 1).

    Supported units:

    • "second";
    • "minute";
    • "hour";
    • "day" (acts as the day of the year if number is specified);
    • "week";
    • "month";
    • "quarter";
    • "year".

    Argument types:

    • datetimeDate | Datetime
    • unitString
    • numberInteger

    Return type: Same type as (datetime)

    Note

    Only constant values are accepted for the arguments (unit, number).

    Note

    The function with three arguments is only available for the sources ClickHouse version 19.13 or higher.

    Examples

    DATETRUNC(#2018-07-12 11:07:13#, "minute") = #2018-07-12 11:07:00#
    
    DATETRUNC(#2018-07-12#, "year", 5) = #2015-01-01#
    
    DATETRUNC(#2018-07-12 11:07:13#, "second", 5) = #2018-07-12 11:07:10#
    
    DATETRUNC(#2018-07-12 11:07:13#, "month", 4) = #2018-05-01 00:00:00#
    

    Data source support

    ClickHouse 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.