sql: support SQL types timestamp(0), timestamptz(0) to round values

[faiverson]

FEATURE REQUEST

Right now it is not possible to use timestamp(0)
In postgres, this rounds to full seconds, and fractions are not stored
timestamp(0) and timestamptz(0) will round the number. So, it is not the same than timestamp and timestamptz that give you the number with the millisecond part.
Is it possible to add this behaviour?

[knz]

@faiverson can you tell us more about where and how you are using this feature?

Note that you can use the type timestamp (without 0) but then create values with <yourtimestamp>::int::timestamp to round off seconds.

For example:

root@:26257/t> select current_timestamp(), current_timestamp()::int::timestamp;
+---------------------------------+--------------------------------+
|       current_timestamp()       | current_timestamp()::INT::TIMESTAMP |
+---------------------------------+--------------------------------+
| 2017-06-07 18:43:28.38594+00:00 | 2017-06-07 18:43:28+00:00      |
+---------------------------------+--------------------------------+
(1 row)
root@:26257/t> select current_timestamp(), current_timestamp()::int::timestamp;
+---------------------------------+--------------------------------+
|       current_timestamp()       | current_timestamp()::INT::TIMESTAMP |
+---------------------------------+--------------------------------+
| 2017-06-07 18:43:28.56454+00:00 | 2017-06-07 18:43:28+00:00      |
+---------------------------------+--------------------------------+
(1 row)
root@:26257/t> select current_timestamp(), current_timestamp()::int::timestamp;
+----------------------------------+--------------------------------+
|       current_timestamp()        | current_timestamp()::INT::TIMESTAMP |
+----------------------------------+--------------------------------+
| 2017-06-07 18:43:28.746146+00:00 | 2017-06-07 18:43:28+00:00      |
+----------------------------------+--------------------------------+
(1 row)

Would this help?

[faiverson]

Laravel uses timestamp(0) to create tables with timestamp columns using the framework.
To use timestamp without (0) I need to create a different provider.
Using timestamp(0) I can use the postgres provider, so it gives me compatibility.
I'm trying to cover all the problems to be able to work with Laravel without extra effort, so the next developer only needs to setup the connection and be able to use laravel with cockroach.

Compatibility with postgres will help devs to switch too

[knz]

cc @cuongdo for roadmapping

[alfredherr]

I'm running into the same issue when using Ecto in Elixir. The framework is emitting timestamp(0) which is rejected by Cockroach. Can this feature be included?

[knz]

Note we are tracking this feature also in #32098.

[jordanlewis]

Closing this as duplicate of #32098.