B4J Question Server and Timezones

[udg]

Hi all,

I'd like to hear from you about how you manage differences in time zones between server and devices, since I can think of a few alternatives and each has its pros and cons.

As long as server and devices share the same TZ there's obviously no problem. On local devices I usually use ADP (AnotherDatePicker) to collect dates, send them as longs to the server which finally stores them on a MySql DB as DATE or DATETIME (in cases when the time component is significant and was added to the ADP result). Looking at data with Toad or similar tool I can see data as originally entered.

When server and devices are on different TZ :

1. One approach could be to prepare on local devices a string in the format expected by the DBMS (e.g. "yyyy-MM-dd"), send it and have the server simply use it as is; the DB will convert from string to DATE.

2. Another approach could be to convert to UTC(0) on the server so to "normalize" data to a common time base. This has the effect that Toad doesn't read the original value but it's conversion to TZ0.

3. Using strings (ok, varchar) instead of date/datetime is another alternative, but I feel it too conservative and limiting in some contexts.

My need is twofold: recording and reading back data irrespective of the TZ where the device currently is (I mean recording 2018-12-31 15:35 in Rome, then moving to NY I should read again 2018-12-31 15:35) and be able to look at as-entered data by Toad.

Hmm, maybe just adding the TZ info to my original data will solve the whole problem..
Anyway, what's your approach?

TIA

 

[AHilton]

Always store UTC at the database level. Never the clients' local time. For B4X apps and most others, I do the local <-> UTC conversions on the client side. The client knows what timezone its' in and that info doesn't have to be stored on the database or transferred between client and server all of the time.

Whether you store that time as something easily readable by database tools as in a DATETIME is up to you. BIGINT for MySQL is what I use if I'm mainly using B4X apps to store the datetime LONGS. Less mess in conversions to appropriate STRINGs that way. If I'm using a mix of app types, then I'll use STRINGs. More flexible (but messy with all of the conversions) that way.

 

[udg]

Thank you, Harris.

So, your solutions is.. 2.5 eheh I mean, using UTC0 but on local device rather than on the server.
About the "proper" type on MySql, if using integers you won't be able to use SQL functions, right? Like "extract all customers born between date1 and 10days after that" or "select any order enterd on same month as date1". I understand you can compute date1, date2 before preparing the SQL statement, but I'm referring to operations available in the SQL language itself.
Up until now I generally used DATE and DATETIME/TIMESTAMP as types, passing parameters as strings.

Would you advice to set the server itself at UTC0 irrespective of its real-world location? This will make it independent from its location, letting you to move it elsewhere if need arises, but will impact on any other service running on it. What do you think?

TIA

 

[Sandman]

I agree with @AHilton, go for UTC. Every single place on the backend (which includes data in the phone), store datetimes as UTC.

Only convert from UTC at the very last moment, when displaying for the user. (And likewise, accept data using the users' regular TZ, but convert it to UTC right away before storing it anywhere.)

Also make sure that your database is running on UTC. As you're using MySQL, you need to check the TZ settings for it. It's entirely possible it will have the TZ "SYSTEM", which means that it will just use whatever your server is running. In that case you need to decide if you're going to change the MySQL TZ or the server TZ. I like doing this hardcore and go for UTC everywhere. I've been too many headaches where I've confused myself, not knowing exactly what TZ a date is.

And if you're using some sort of framework for your API, check what TZ that uses and set it to UTC also.

Also, if you don't need higher resolution, just go for DATETIME in the database. Keep it simple.

 

[Harris]

I hear ya...

Use Long everywhere (BIGINT - or whatever it is called by different sql's). Store UTC (0) on the server.
Leave the device clock set to it's local time. The datetime written to device is stored as a UTC value - taking in account the TZ offset.

I struggled with this for some time - and was confounded (Erel helped - but still confused).
When the server is UTC, the browser client (or device) will figure out the time difference.
Use Datetime.Now on your devices (local time) and send that to the server.

For example:

I live in BC (-8 GMT). I wrote a record at 1 pm and sent it to the server.
A user in AB viewed this record and saw it as I posted it at 2 pm - he is at -7 GMT. This is correct from his point of view.

MySQL has a function - "FROM UNIXTIME". I must use this when writing scripts in Next Reports (and others) for it to parse this Long into the format it needs...

From my perspective, there is nothing more confusing (and potentially more problematic) than date / time....

The (Canadian) fellow who invented time zones (many years ago) had no idea had this would affect us now - in the digital age.

I still struggle with DST to Standard time ( 1 hour diff ) when a person crosses this during the change.

Thanks

 

[udg]

Hi @Harris , thank you for sharing your experience.

I am considering to set the server's timezone to GMT(0) irrespective of its real location. MySQL will have a TZ System setting so to accomodate for the server's one.
This way, whenever I should move my server elsewhere (or simply duplicate it) everything will stay the same and I wll not have to worry about it.
Coming to code, it seems that everyone favors the conversion from local time to UTC (and back) be operated on local device before sending data to server.
And finally, most of you are satisfied with BIGINTS as a type not generally needingany of the special date/time function made available by the DBMS.

So, I could plan things this way:
1. Read in any data from components (ADP, EditBox, other) as is
2. Convert the readings to UTC
3. Send data from point 2 as Long and store them as BIGINTS or as DBMS "date string" (e.g. yyyy-MM-dd HH:mm:ss) and store them as DATETIME/DATE
4. Inspecting data with tool like Toad be prepared to read it represnted as for GMT(0) and mentally convert to my current timezone+/-DTS.

Thanks to all

 

[Harris]

Please, someone correct me if I am wrong with this assumption:

When a Long (datetime.now - for example) is stored to a SQLite table on my (Android) device - it is stored as UTC - NOT the local time.
When I read this value on the device, Datetime.Date and Datetime.Time use the TZ offset on the device to show these values correctly.

Thanks

 

[LucaMs]

When a Long (datetime.now - for example) is stored to a SQLite table on my (Android) device - it is stored as UTC - NOT the local time.

So, if I need to store a Long which is my age , it is saved as UTC?

No, DateTime.Now returns a Long which represents the number of milliseconds since 1.1.1970 UTC and you store this value.

 

[LucaMs]

"Nice question", for which I should open a new thread

Dim Ticks As Long = DateTime.DateParse("01/01/1970")
Log("Ticks parsing 01/01/1970: " & Ticks)
Log("Date based on Ticks: " & DateTime.Date(Ticks))
Log("---")
Log("Date based on -3600000 ticks: " & DateTime.Date(-3600000))
Log("Date based on 0 ticks: " & DateTime.Date(0))

Log:
Ticks parsing 01/01/1970: -3600000
Date based on Ticks: 01/01/1970
---
Date based on -3600000 ticks: 01/01/1970
Date based on 0 ticks: 01/01/1970


Also...
Roma = UTC+1 (+ "summertime")
-3.600.000 is -1 hour.