[EPIC] A collection of Date/Time related open issues

[waitingkuo]

This is followed on the Proposal for Date/Time enhancement #3100
Please edit the list or comment bellow if you find something not listed here.

Date/Time/Timestamp Types & Casting

• Add support for TIME literal values #2883 - This is the initial work for TIME literal (i.e. make SELECT TIME '00:00:00'; work)
• Casting between timestamp types should use scalar multiply/divide kernels arrow-rs#2074 - This is to improve the performance of casting timestamp between different unit
• [Datafusion] Error with CAST: Unsupported SQL type Time #193 - This depends on Add support for TIME literal values #2883
• Cast string to timestamp crash while we input time before 1970 with floating number second #3082
• Support to_timestamp with 2 arguments (timestamp format) #5398
• Support float arguments to to_timestamp function #7868
• date_trunc always returns Timestamp(Nanosecond, None) which might truncate ranges #6653
• date_trunc(null) results in a panic #6701
• Incorrect results for negative intervals #6720

TimestampTz Date Types & Casting & Coercion

• Can not create a TimestampNanosecondArray that has a specified timezone arrow-rs#597
• Format timestamps that have a timezone using RFC339 format arrow-rs#599
• Implement FromIter for timestamps, that includes timezone info arrow-rs#168
• Querying datetime data in DataFusion with an embedded timezone always fails #153
• Should Cast(UTF-8 AS Timestamp) apply local time zone? #3080
• SELECT ... ORDER BY query fails on data with int64 timestamp and timezone field #959
• cast to timestamp with time zone drops timezone #3149
• Add Time Zone support datafusion-sqlparser-rs#588
• Not possible to have timezone in postgres datafusion-sqlparser-rs#464
• Add TIMESTAMPTZ #3659
• convert string to timestamp shouldn't apply local timezone offset if there's no explicit timezone info in the string arrow-rs#2813
• Support SQLDataType::Timestamp(TimezoneInfo) #3693
• Time With Time Zone should raise error until DataType::Time64 support tz #3715
• Add timezone abstraction arrow-rs#2909
• support [+/-]hhmm and [+/-]hh as fixedoffset timezone format arrow-rs#2910
• Cast Kernel Ignores Timezone arrow-rs#1936
• Support for type coercion for a (Timestamp, Utf8) pair #4311
• Interval coercion:date_bin('1 hour',...) does not work but date_bin(interval '1 hour', ... does #4853
• Supporting casts to timestamptz: Eq Utf8 of binary physical should be same" #5164
• try_cast_literal_to_type doesn't respect timestamp precision #5507
• Special case Duration display in datafusion-cli and sqllogictest #7070
• date_bin cannot use a string origin parameter when the expression has a time zone. #7697

Intervals

See #5753

Arithmetic (including intervals)

• Add support for AVG(Timestamp) types #200
• Add support for subtracting timestamps --> intervals #194
• feat: support Timestamp +/- Interval #3103
• Interval plus(+) Timestamp operation support #5413
• functions in temporal_conversions do not handle negative integers correctly arrow-rs#593
• Add temporal kernels for arithmetic with timestamps and durations arrow-rs#527
• Interval is out of spec arrow-rs#187
• Support Time64/Time32 comparison arrow-rs#2457 - arrow-rs
• Support Time comparison #3157 - datafusion
• Support SUM AVG, MIN, MAX on Time columns.  #3166
• Interval Literal output inconsistent date_type #3180
• Incorrect Numeric operations on MONTH_DAY_NANO interval type arrow-rs#1065
• Interval Literal doesn't work for timeunit less than millisecond #3204
• Incorrect error for plus/minus operations over timestamps and dates  #4420
• Comparing a Timestamp to a Date32 fails #4644
• Timestamp Subtraction #5411
• timestamp - interval results in The type of Timestamp(Nanosecond, Some("+00:00")) Minus Utf8 of binary physical should be same #5650
• Timestamp and Interval arithmetics #5704
• support make_interval function #6951
• Use Duration as the result of timestamp - timestamp rather than Interval #7068
• Support binary temporal coercion for Date64 and Timestamp types #8275

Functions

• date_part does't work for now() #3096
• once now() is called in a statement, it forever returns the same value #3057
• to_timestamp timeunit to be consistent with postgresql's #2979
• The return type of EXTRACT #1992
• Predicates on to_timestamp do not work as expected with "naive" timestamp strings #765
• Specific timezone support for to_timetamp*()  #686
• Handle the timezone in extract week in temporal.rs arrow-rs#1380
• Predicates on to_timestamp do not work as expected with "naive" timestamp strings #765
• Paniced at to_timestamp_micros function when the timestamp is too large. #3832
• replace from_timestamp by from_timestamp_opt arrow-rs#2892
• Can not use extract <part> on the value of now() #3980
• Implement current_date Function #3981
• Implement current_time Function #3982
• change extract return types to decimal to align with postgres #3996
• change date_part return types to f64 #3997
• support keyword NANOSECOND datafusion-sqlparser-rs#748
• Extract from timestamp doesn't support nanosecond #4528
• EXTRACT(EPOCH from column) #2785
• Support date_bin with 2 arguments (rather than requiring three) #5641
• Implement to_unixtime function #5568
• support: Date +/plus Int or date_add function #6876
• Timestamp with timezone cannot be supported by GROUP BY #7604

parquet/csv/json

• Reading Parquet file with timestamp column with 9999 year results in overflow panic arrow-rs#982
• Parquet Scan Filter arrow-rs#1191
• csv reader does not detect ISO date strings as timestamps arrow-rs#1060
• Not able to read nano-second timestamp columns in 1.0 parquet files written by pyarrow arrow-rs#455
• Read temporal values from JSON arrow-rs#38
• wrong result when operation parquet #2044
• Cannot import Time64 from CSV #3176

others

• IPC date serialization issue arrow-rs#902
• Add support for get / show timezone #3255
• can not parse "SET TIME ZONE 'UTC'"  datafusion-sqlparser-rs#303
• Support Time32 and Time64 for Type Coercion #4104

[waitingkuo]

@alamb @avantgardnerio i created a separated ticket for tracking timestamp related open issues (from both datafusion and arrow-rs)

[alamb]

This is amazing @waitingkuo -- thank you

[liukun4515]

@waitingkuo you can @ me to review your pr about the times.
I care about this part

[avantgardnerio]

Proposal:

We attack this in multiple steps parts:

1. Add timestamp but not timestamp with timezone
2. Hard code the server "local" time zone to always be UTC, such that show time zone always returns UTC and set time zone 'UTC' is the only set command that will not produce an error
3. Add timestamp with timezone, but it will always be UTC due to the above
4. Add support for set time zone 'UTC+8'
5. Add support for set timezone 'US/Mountain

I think the above plan will allow us to tackle the problem in stages, without ever technically breaking ANSI compliance and reaching full compliance at the end state. Caveat: I'm not sure if we will ever reach the end state, and I'm okay with that personally.

[alamb]

Thank you @avantgardnerio and @waitingkuo for driving this issue. It is awesome to see

[waitingkuo]

@alamb @avantgardnerio @liukun4515 i just finished the first 2 from @avantgardnerio's proposal

1. Add timestamp but not timestamp with timezone
2. Hard code the server "local" time zone to always be UTC, such that show time zone always returns UTC and set time zone 'UTC' is the only set command that will not produce an error

now we have

DataFusion CLI v12.0.0
❯ show time zone;
+--------------------------------+---------+
| name                           | setting |
+--------------------------------+---------+
| datafusion.execution.time_zone | UTC     |
+--------------------------------+---------+
1 row in set. Query took 0.059 seconds.

i'm just starting the work for timestamp with timezone, things become trickier now

postgresql has this
https://www.postgresql.org/docs/current/functions-formatting.html

to_timestamp ( text, text ) → timestamp with time zone

Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.32.)

to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

and this
https://www.postgresql.org/docs/current/functions-datetime.html

to_timestamp ( double precision ) → timestamp with time zone

Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone
to_timestamp(1284352323) → 2010-09-13 04:32:03+00

Looks like its to_timestamp always return timestamp with time zone

In general, do you think we should align these function signatures with postgresql?
There will be more things to change once we have timestamp with non-UTC timezone 💔

BTW
Datafusion's now() output timestamp with time zone which is same as what postgresql has 👍

[alamb]

In general, do you think we should align these function signatures with postgresql?
There will be more things to change once we have timestamp with non-UTC timezone 💔

I think it would be ok to align the functions with postgres as long as there is some clear way to get the current behavior as well (e.g. the query needs to be tweaked or something)

[alamb]

I filed #3980 and added it to this ticket -- I plan to add more date/time open issues here to help track them

[mjvankampen]

I think #2785 belongs in this list as well!

[waitingkuo]

added, thank you @mjvankampen

[alamb]

Filed See #5753 to track work for intervals

[watfordkcf]

We're ramping up our usage of delta-rs (and with it arrow-datafusion) and would like to see resolved much of the weirdness with dates/times (ignoring the absolute pain Spark brings upstream). Don't want to duplicate effort, so would help with #959 (and #6876) be welcomed?

[alamb]

Hi @watfordkcf -- thanks!

I believe @tustvold is in the middle of cleaning and making consistent the various timestamp arithmetic / kernels / intervals

The largest outstanding gap that I know of is correct timezone handling (basically anything other than UTC timestamps are like to hit corner cases / bugs) -- this is related to #959

In terms of #6876, that would be lovely I think.

[liukun4515]

Do we have any method to convert or cast the integer type/integer expr with the timeunit to the interval?

@waitingkuo @alamb

I want to implement a app or function like this datetime + integer with timeunit, and the timeunit may be year,month,day.

I can't find the any method to convert the integer with timeunit to the interval, but in the PG which has a function make_interval() https://www.postgresql.org/docs/current/functions-datetime.html can do this.

I just can find a way to resolve the issue by using the concat method.

For example: I have a table like below, and want to convert the b with dayunit to interval

❯ \d test
+---------------+--------------+------------+-------------+-----------+-------------+
| table_catalog | table_schema | table_name | column_name | data_type | is_nullable |
+---------------+--------------+------------+-------------+-----------+-------------+
| datafusion    | public       | test       | a           | Date32    | NO          |
| datafusion    | public       | test       | b           | Int64     | NO          |
+---------------+--------------+------------+-------------+-----------+-------------+
2 rows in set. Query took 0.013 seconds.

Using the concat to get the utf8 expr which can be casted to interval.

❯   select cast(concat(b,' day') as interval), b from test;
+-------------------------------------------------------+---+
| concat(test.b,Utf8(" day"))                           | b |
+-------------------------------------------------------+---+
| 0 years 0 mons 2 days 0 hours 0 mins 0.000000000 secs | 2 |
+-------------------------------------------------------+---+

[alamb]

@liukun4515 there is the interval constant syntax:

DataFusion CLI v27.0.0
❯ select interval '5 days';
+-------------------------------------------------------+
| IntervalMonthDayNano("92233720368547758080")          |
+-------------------------------------------------------+
| 0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.025 seconds.

Also casting strings to intervals handle units

❯ select '5 days'::interval;
+-------------------------------------------------------+
| Utf8("5 days")                                        |
+-------------------------------------------------------+
| 0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.003 seconds.

Or creating a string via || (which is the same as the concat you mention above)

❯ select (5 || ' days')::interval;
+-------------------------------------------------------+
| Int64(5) || Utf8(" days")                             |
+-------------------------------------------------------+
| 0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.004 seconds.

[liukun4515]

@liukun4515 there is the interval constant syntax:

DataFusion CLI v27.0.0
❯ select interval '5 days';
+-------------------------------------------------------+
| IntervalMonthDayNano("92233720368547758080")          |
+-------------------------------------------------------+
| 0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.025 seconds.

Also casting strings to intervals handle units

❯ select '5 days'::interval;
+-------------------------------------------------------+
| Utf8("5 days")                                        |
+-------------------------------------------------------+
| 0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.003 seconds.

Or creating a string via || (which is the same as the concat you mention above)

❯ select (5 || ' days')::interval;
+-------------------------------------------------------+
| Int64(5) || Utf8(" days")                             |
+-------------------------------------------------------+
| 0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.004 seconds.

5|| ' DAY' is same with concat( 5, ' day') ?

I use this method concat to complete my requirements, but the performance may be not better.

#6876 (comment) also provide the thoughts about support the make_interval function

cc @watfordkcf

[alamb]

Wow the list of tickets fixed is quite impressive:

[alamb]

I have moved all the incomplete items and moved them to #8282 to help track what is remaining. Thanks everyone for the epic work here