Issue parsing dates

[schlegelp]

Hi! Reading date columns throws multiple warnings.

Consider the following table with a couple empty (None) rows:

                my_date
0  2021-12-01T00:00:00Z
1  2021-12-01T00:00:00Z
2                  None
3                  None
4                  None

Fetching the my_date column throws two types of warnings:

>>> base.query('SELECT my_date FROM MyTable')
[Warning] format date: Invalid isoformat string: '2021-12-01T00:00:00Z'
[Warning] format date: Invalid isoformat string: '2021-12-01T00:00:00Z'
[Warning] format date: fromisoformat: argument must be str
[Warning] format date: fromisoformat: argument must be str
[Warning] format date: fromisoformat: argument must be str

[{'my_date': '2021-12-01T00:00:00Z'},
 {'my_date': '2021-12-01T00:00:00Z'},
 {'my_date': None},
 {'my_date': None},
 {'my_date': None}]

1. Invalid isoformat string:

The culprit for this appears to be this line:

seatable-api-python/seatable_api/utils.py

Line 139 in 9922141

date_value = datetime.fromisoformat(value)

Apparently, datetime.fromisoformat does not like the trailing Z (see this post on stack overflow):

2. Argument must be a str:

The problem here is that the same try/except block as above does not cater for empty rows.

These issues are obviously not show stoppers but since each row will cause a warning (that can't be suppressed because it's a print statement) this becomes really annoying for longer tables.

On a general note: Is that implicit (re-)formatting strictly necessary? For example, I'm typically converting data into pandas data frames anyway and pandas.to_datetime() has no issues with the full string - even parses the correct time zone:

>>> pd.to_datetime('2021-12-01T00:00:00Z')
Timestamp('2021-12-01 00:00:00+0000', tz='UTC')

FYI: This is with seatable_api version 2.5.1.

I also have a separate question on writing dates to SeaTable: I've tried writing a timestamp back to the table (via batch update) and it appears as if for iso-formatted strings (e.g. 2021-12-01T11:00:05Z) the date is correct ingested but the time is dropped. Can you tell me what the expected format is?

Thanks,
Philipp

[freeplant]

To write value to SeaTable rows, you can refer https://seatable.github.io/seatable-scripts/data-structure/ .

For date, it should be 2020-01-01 or 2020-01-01 10:00 . Without timezone information. In the server side, it will use local timezone of the server to handle date and time.

[schlegelp]

In the server side, it will use local timezone of the server to handle date and time.

Ah! So the Z suffix for UTC in my case because of me using a self-hosted SeaTable?

[freeplant]

For 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.

[freeplant]

[Warning] format date: fromisoformat: argument must be str

This warning is removed in v2.5.2.

[jefferis]

@freeplant thanks for earlier comments. I have also run into this recently. For the same self-hosyed server running in UK timezone (and therefore UTC in winter and UTC+1 in summer) I see a mix of formatting based on whether the datetimes are in summer or winter when doing SQL queries.

My hypothesis is that they look like something like this:

2021-12-12T06:53:00Z
2021-06-23T07:01:00+01:00

when passed to datetime.fromisoformat and that the former fails and the latter works. So I think this needs a change within the python package to fix. This would print to console

[Warning] format date: Invalid isoformat string: '2021-12-12T06:53:00Z'