The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

date_trunc return type is timestamp, even if argument is date

Vertica 10.1 docs saying about date_trunc:

The return value is the same data type as the input value. trunc

At same time, return from date_trunc with date argument is timestamp:

select date_trunc('year','2020-01-01'::date);
2020-01-01 00:00:00.000000

Attempt to create table partitioned by date_trunc('year', date_column) gives warning:

17:05:18 WARNING [CREATE - 0 rows, 0.345 secs] OK. No rows were affected
Code: 6100 SQL State: 42611 --- Using PARTITION expression that returns a Timestamp value
Hint: This PARTITION expression may cause too many data partitions. Use of an expression that returns a more accurate value, such as a regular VARCHAR or INT, is encouraged

This is for partitioning clause "partition by date_trunc('year',msg_dt)"

I would totally expect output of date_trunc be of same type as input argument.
What is wrong, is it a wrong docs, or bug in function date_trunc?


  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited April 2021

    Looks like Vertica is impliclity converting your date to a TIMESTAMP to match the argument type of the DATE_TRUNC function

    verticademos=> \df date_trunc
                                 List of functions
     procedure_name | procedure_return_type  |    procedure_argument_types
     date_trunc     | Interval Day to Second | Varchar, Interval Day to Second
     date_trunc     | Interval Year to Month | Varchar, Interval Year to Month
     date_trunc     | Time                   | Varchar, Time
     date_trunc     | TimeTz                 | Varchar, TimeTz
     date_trunc     | Timestamp              | Varchar, Timestamp
     date_trunc     | TimestampTz            | Varchar, TimestampTz
    (6 rows)

    If you want a DATE, uou can just do this:

    partitioned by date_trunc('year', date_column)::DATE

    verticademos=> SELECT date_trunc('YEAR', SYSDATE), date_trunc('YEAR', SYSDATE)::DATE;
         date_trunc      | date_trunc
     2021-01-01 00:00:00 | 2021-01-01
    (1 row)
  • Makes sense, thanks.
    Looks like docs for 10.1 are wrong:
    "Truncates date and time values to the specified precision. "
    Apparently, date_trunc does not take date as argument.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited April 2021

    Technically it is returning the data type (i.e. TIMESTAMP) that it's getting because Vertica converted the original DATE to a TIMESTAMP...

    See "Implicit Casting"

    You could always create your own function to return a date!

    verticademos=> CREATE OR REPLACE FUNCTION my_date_trunc(x VARCHAR, y DATE) RETURN DATE
    verticademos-> AS
    verticademos-> BEGIN
    verticademos->   RETURN date_trunc(x, y)::DATE;
    verticademos-> END;
    verticademos=> SELECT my_date_trunc('YEAR', SYSDATE::DATE);
    (1 row)

    But it's probably easier just to explicity convert the result of DATE_TRUNC back to a DATE :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file