`interval` cast has some problematic behaviour

[samuelcolvin]

Describe the bug

There's a few issues with casting to an interval.

Specifically:

• '1'::interval -> 0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs - postgres returns 1s, I think other databases do the same - this is the worst offender IMHO
• '1s'::interval gives an error, with postgres it returns 1s
• '1second'::interval gives an error, with postgres it returns 1s

Happy to try to fix this if there's willingness? In particular, I think '1'::interval should be fixed asap.

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[alamb]

Seems reasonable to me

I verified the postgres behavior:

psql (14.12 (Homebrew), server 16.1 (Debian 16.1-1.pgdg120+1))
WARNING: psql major version 14, server major version 16.
         Some psql features might not work.
Type "help" for help.

postgres=# select '1'::interval;
 interval
----------
 00:00:01
(1 row)

postgres=# select '1s'::interval;
 interval
----------
 00:00:01
(1 row)

[ozankabak]

It would be great to fix these issues, thanks for offering to help.

[nix010]

take

[nix010]

@samuelcolvin Just want to see what is scope that we want to cover for this case. Are we ok with just second?

[samuelcolvin]

@samuelcolvin Just want to see what is scope that we want to cover for this case. Are we ok with just second?

I think fixing one case doesn't really help.

I've proposed what I think is a comprehensive fix for this in apache/arrow-rs#6211.