Timezone offset ignored when loading into sqlite

[peterdesmet]

Overview

I have a data package with timestamps that have timezone offsets, e.g.:

2020-09-09T07:10:20+02:00

The spec for this fields is:

{
  "name": "timestamp",
  "type": "datetime",
  "format": "%Y-%m-%dT%H:%M:%S%z"
}

When saving this data package to sqlite, timezone offsets get ignored and are saved in sqlite as:

2020-09-09 07:10:20.000000

Retaining timezone offsets is not possible in sqlite's datetime strings, but it would be good if timezones are converted to UTC before uploading to sqlite:

2020-09-09 05:10:20.000000

Is that possible?

---

Please preserve this line to notify @roll (lead of this repository)

[roll]

Hi,

Have you tried it with Frictionless?

This is a legacy codebase

[peterdesmet]

No, do you mean using to_spss()? In the documentation I see:

Wriring to SQL [sic]

[NOTE] Timezone information is ignored for datetime and time types.

[roll]

@peterdesmet It's a typo in the SQL Tutorial - package.to_sql - https://github.com/frictionlessdata/frictionless-py/blob/master/tests/plugins/test_sql.py#L123

But yes the timezones don't seem to be supported

[roll]

We have an issue for it - https://github.com/frictionlessdata/frictionless-py/issues/405 - but not yet ready

[peterdesmet]

Thanks, will close this issue here in favour of the other issue.