Entering 'now' on a datetime column without timezone support shows different time from what user expects

[pavish]

Description

• Edit a datetime column with type 'timestamp without time zone'
• Notice the Date picker showing the client local time in the 'now' button. Eg., 19/09/2022 20:54
• The request sent to the server contains 'now'.
• The server response contains the value with a timezone attached (belonging to the server). Eg., 2022-09-19T15:24:54.468139 AD
• The frontend discards the timezone since the column is configured not to contain time zone information and displays 19/09/2022 15:24 while the user expects 19/09/2022 20:54.

Note: For reproducing this bug, the browser timezone should be different from the Mathesar DB's timezone.

Behaviour when direct value is entered

• Instead of 'now', type in 19/09/2022 20:54.
• Notice the request sent to the server being 2022-09-19 20:54:00.
• The response sent from server after saving is 2022-09-19T20:54:00.0 AD.
• This is displayed correctly because the timezone offset here is 0.

Expected behavior

• The value expected by the user needs to be shown to the user.

Discussion - options

• Should the frontend send the time for 'now' instead of now? This will result in other postgres keywords unable to translate to the client timezone.
• Should the frontend also consider the timezone for this column? But in this scenario, a different client in a different timezone will see a different time, even though this column is supposed to only show the same static datetime values everywhere. This would go against the usefulness of this column type.
• Can the backend take the client timezone information from request, and store & return the value accordingly in responses? [This would make it a lot easier for the frontend]

[pavish]

@mathemancer I assume fixing this issue will need some thought/discussions.

@kgodey For the demo video, can we create the required columns with type 'timestamp with timezone' (We'll have to check 'Support Time Zones' checkbox in Database options)? This bug only occurs on columns with type 'timestamp without timezone'.

This way, we can move this issue to the live demo milestone.

[kgodey]

Yes, that's fine @pavish, let's put this in the live demo milestone.

[mathemancer]

A couple notes:

• The response you showed doesn't contain a timezone. I think you're confusing that with the 6-decimal precision for seconds.
• Timestamp without time zone absolutely doesn't add any TZ.
• Because the server is in UTC (by default), NOW() records a time according to the server's time, without any TZ info, then returns that.
• There's no way for the server to know what NOW() means to the client, so it must go by its own clock (assuming we call NOW()).
• I'm not sure how timestamp with timezone will help in this case. That type is not recommended for use ever, because it rarely does what one expects.

Example:

mathesar=# CREATE TABLE tztest (id SERIAL PRIMARY KEY, col1 TIMESTAMP WITH TIME ZONE, col2 TIMESTAMP WITHOUT TIME ZONE);
CREATE TABLE
mathesar=# INSERT INTO tztest (col1, col2) VALUES (NOW(), NOW());
INSERT 0 1
mathesar=# INSERT INTO tztest (col1, col2) VALUES ('2022-09-20 14:30:00+08', '2022-09-20 14:30:00');
INSERT 0 1
mathesar=# SELECT * FROM tztest;
 id |             col1              |            col2
----+-------------------------------+----------------------------
  1 | 2022-09-20 06:27:41.004964+00 | 2022-09-20 06:27:41.004964
  2 | 2022-09-20 06:30:00+00        | 2022-09-20 14:30:00
(2 rows)

What TIMESTAMP WITH TIME ZONE does is:

Allow you to input a time from the perspective of a given TZ (not possible with NOW() under any case; it wouldn't make any sense). It does not, however, store the time in that timezone. it stores it in UTC. Then, when you query, it reports the time in ~UTC~ the TZ configured for the database (or session). However, this is unrelated to the timezone in which you store the time. As shown in the example, this is pretty much always worse than storing it without a time zone at all (unless you're specifically trying to work across timezones, and want (for some reason) to avoid needing to support the TZ clientside for writing (though you'd still have to for reading in most cases).

[mathemancer]

After some discussion in other channels, we aren't going to need any back end work on this for the moment.

[kgodey]

Moving this to the Backlog – this isn't critical enough to block launch.

[github-actions[bot]]

This issue has not been updated in 90 days and is being marked as stale.