date_trunc return type is timestamp, even if argument is date

scherepanov · April 2021

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?

Jim_Knicely · 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)

scherepanov · April 2021

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_Knicely · 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;
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

We're Moving!

Create My New Community Account Now

date_trunc return type is timestamp, even if argument is date

Comments

Leave a Comment