I want vapor to support time zone.
Details are in below.
Problem
Database level debuggability
Currently vapor supports to reading and writing Date
value for database.
But it is always encoded as GMT offset representation.
So when I get data in MySQL database directly (e.g. via SELECT
query),
its difficult to read actual meaning of value in my local timezone.
It is no problem during developing server side application with vapor,
But sometimes I need to read DB directly.
For low level debugging, manual data operation, etc.
Native time zone setting support
MySQL has time zone setting.
It affects some MySQL functions such like NOW()
.
https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
With this setting, MySQL datetimes value mean in this time zone.
For example, when the time at 2018/04/27 14:00:00
in JST (+09:00),
NOW()
with time_zone = '+9:00'
returns '2018-04-27 14:00:00'
,
with time_zone = '+0:00'
returns '2018-04-27 05:00:00'
.
Because vapor always represent Date
value with GMT,
a value from Date()
in Swift and a value from NOW()
in query are different.
They should be equal.
Solution options
I have some approaches to solve this problem.
But all of them have other problem, are not perfect.
I want to read what you think.
1: Data convertion with time zone parameter
DatabaseConnection
have a new property timeZone: TimeZone?
.
convertToMySQLData
and convertFromMySQLData
in MySQLDataConvertible
protocol take new parameter timeZone: TimeZone
.
queryDataSerialize
and queryDataParse
in QuerySupporting
protocol take new parameter timeZone: TimeZone
.
In alternative, make new MySQLDataConvertion
type and QueryDataConvertion
type and hide timeZone
proeprty in them. these convertion functions takes this as a new parameter.
Actually I tried this approach in first time,
But with this change, I need to add parameter also QueryBuilder.filter
functions.
Its really bad to user, and,
DatabaseConnection
is need when build query before execute it.
While it makes more heavy change,
if we change filter
implementation to delay serialization of value in QueryFilterValue
at execution time,
this approach will reach to ideal solution I think.
So it means change case data(Database.QueryData)
to case data(T)
and
calling Database.queryDataSerialize
delays to in execution time
such like QueryBuilder.create
.
2: Introduce ZonedDate
type
If we introduce new type ZonedDate
which has Date
and TimeZone?
,
encoding to MySQL representation with this time zone can be achieved
without any other new parameter injection.
The fatal problem with this is there is no way to know time zone
when we decode ZonedDate
from datetime in MySQL.
In this approach, keep timeZone: TimeZone? == nil
from decoding.
it is means "Date are decoded but we dont know time zone yet,
and need to know time zone if we use this value as date value".
And after decode, timeZone
is filled with database timezone which stored in DatabaseConnection
using to execute query.
This timeZone filling operation may implemented in QueryBuilder.run
.
But I don't understand that can be with Swift type system and do with low runtime cost.
3: Use Date
with offset and improve Timestampable
Using Date
with offset solve half of problem.
For example, if I want to represent date with JST(+9:00)
,
I just need to offset Date
with 9 * 86400
simply.
It will encoded to time in JST literally.
To use this approach, we improve Timestampable
function to work
with this offset technique.
QueryBuilder.create
make a Date
to update fluentUpdatedAt
with "now".
But in this offset convention, this value also offset.
To achieve this, I propose that a new property
static var timeZone: TimeZone? { get }
in Timestampable
.
This approach is simple and works.
But using Date
with multiple offset convention is very confusable
and may cause to mistake handling makes bug in application.
Conclusion
Tell me what do you think about problem and
show direction how to solve this.
If core team decide direction and it allowed,
I can contribute to implement it.
Related
t.ae is my colleague and his this post looks same problem.
This is our really problem in our job.
https://github.com/vapor/mysql/issues/168