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
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)::DATEverticademos=> 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.
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