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.
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
Looks like Vertica is impliclity converting your date to a TIMESTAMP to match the argument type of the DATE_TRUNC function
If you want a DATE, uou can just do this:
partitioned by date_trunc('year', date_column)::DATE
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.
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!
But it's probably easier just to explicity convert the result of DATE_TRUNC back to a DATE