How timezone is treated in SeaTable

Time storage

The time stored in date column has no timezone information. The time stored in create time column and last modified time column has timezone information (UTC time).

Web

Display

  • Date column: display the stored value directly
  • ctime and mtime column: display the stored value using browser’s local timezone

Comparison

  • Date column: Compare directly
  • ctime and mtime column: Convert the value using browser’s local timezone and then compare

SQL

SQL Query result

  • Date column: The string stored in date column have no timezone information, if time part contained in the string, it is treated as the time at the server’s timezone, and converted to UTC time and returned. If no time part contained in the string, it always returned as 00:00:00Z.
  • Create time column and modified time column: The string stored contains time zone information, the correct ISO format time will be returned.

SQL Time comparison

  • The user input time (no timezone information) is treated as the time at the server’s timezone
1 Like

Hi @daniel.pan,

One of the side effects is the behaviour below.

Steps to reproduce:

  1. Create a Date column, using the default settings. Enter a date into the cell.
    My Example: 2022-06-15
  2. In the Date column, select “Customize Column Type”, and toggle “Accurate to Minute” on.
    My Example: 2022-06-14 18:00
    I assume that this is because 00:00:00Z is (correctly) 18:00 the day before in my time zone.
  3. In the Date column, select “Customize Column Type” again, and toggle “Accurate to Minute” off.
    My example: 2022-06-14
    I assume that the application just truncates and discards the time.

Now, of course, the date contained in the cell has moved back by a day. Every time the steps above are completed, the date contained in the cell moves back another day.

As an enhancement or bug fix, consider making the “Accurate To Minute” toggle time value default to midnight in the browser timezone, or better yet, have the toggle switch only affect the way the data is shown, instead of changing the cell value.

Thanks,

Peter