DATETRUNC

    Date/Time functions

    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"
    • "year"

    Argument types:

    • datetimeDate | Datetime
    • unitString
    • numberNumber (whole)

    Return type: Same type as (datetime)

    Note

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

    Note

    A function with three arguments is only available for the sources Materialized Dataset, ClickHouse version 19.3.3 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

    Materialized Dataset, ClickHouse 1.1, PostgreSQL 9.3