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.

https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_TRUNC.htm?zoom_highlight=date 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
SQLWarning:
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?

Comments

  • Jim_KnicelyJim_Knicely Administrator
    edited April 13

    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 Administrator
    edited April 14

    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;
    CREATE FUNCTION
    
    verticademos=> SELECT my_date_trunc('YEAR', SYSDATE::DATE);
     my_date_trunc
    ---------------
     2021-01-01
    (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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.