Date fields and timezones issue (Unix vs UTC conflict)
Dear Ninox members,
I would like to share my experience with other users as well as ask for improvement features from Ninox support in upcoming updates regarding Date fields that are affected by timezone setting in the device. I have checked and experemented on iPhone and iPad using both Ninox App and Ninox cloud through browser. Ninox and iOS versions up to date.
1) Ninox date fields store dates in Unix format https://en.m.wikipedia.org/wiki/Unix_time you can check that by making a formula field and putting number(today()) or format(today(), "x") where today() is a function that returns Date value. You can refer to your your Date field as an alternative.
2) Storing Date value in Unix is the same as to store Date and Time value. Every time you put a Date in a Date field it stores 00:00 time next to it without user knowing about it. Unix format value in programming is called timestamp value and falls into Datetime type. So it is not 100% accurate where in Ninox manuals it says that Date fields are of Date type. In other words Date fields are the same as Date / Time with the only difference that it leaves out Time value and uses 00:00 by default.
3) Unix timestamp value is affected by Timezone settings in the device. This is very important because it allows to work internationally. For example if a record was created in a country UTC+2 at 15:00 then another team member living in country UTC+1 will see it as 14:00. Although time is different, but it shows at what moment it was created for both of team members. https://en.m.wikipedia.org/wiki/Coordinated_Universal_Time
4) Although for Time fields this is convenient, but for Date fields this brings a major problem. Because every Date value in the database has 00:00 Time value hidden as soon as device Timezone setting changes backwards (and usually everybody have it to automatically change, because otherwise time stops being accurate after a while) all those Dates shifts 1 day backwards with hidden Time value of 23:00.
5) Problem persists even more when new records are created while being in a different Timezone because those records will have 00:00 hidden Time value that will turn to anything like 02:00 or 04:00 after Timezone will return to original. This will not change Date value, but it is still a big problem because it does not equals to same Dates with different timestamp. For example you decide to change table view by grouping Date column. You would expect to see all the records of same date in same group, but it does not happen with some dates have different timestamp that you even cannot see unless you convert it to number or format it to datetime or Unix.
Solutions I see:
A) Store the Date in a text format and loose all the capabilities of Date field
B) Make manual Timezone and do not switch to daylight saving time as it is the same as switching timezone from UTC point of view
C) Do nothing and keep in mind that Dates are dynamic and can change every half a year even if you do not travel
D) Put a snippet code inside every Date field you create in “Trigger after update” like Date := Date + timeinterval(3600000*12) where Date is your Date field’s name and 12 is how many hours it will add. This snippet will add 12 hours to your Date value every time you update it. This will make your Dates consistent, but it will not solve grouping problem, that is some dates will still be not equal to each other even having same Date.
E) Ninox support will reconsider storing Date values in Unix format or make it store UTC value along with timestamp value. If this would be implemented I would like to see a setting in Date / Time fields that would allow to choose between local and global options as well. For example if somebody would create an Event in London on 18:00 and his team members in Paris would see it as intended and not 17:00 by Paris Timezone.
It was such a shock for me when one day I saw big part of my records having wrong dates! I spent a lot of time to find the problem and research for solutions. I hope my contribution will be appreciated as I believe this is the first topic in relation to this problem.
21 replies
-
We are aware of this problem and are working on a solution.
Birger
-
Thank you for your detailed explanation of what is going on. That has relieved a lot of stress just to know! And now I know how I can work around it when it’s really important. So thanks for taking the time to post!
-
Dear Ninox, I am returning to this thread, because I continue experience unexpected behaviour with dates (I am the original thread creator).
Since my last posts (I used only offline iOS app at that time) I have now started using Ninox cloud. I have created database for my company and bought 5 users subscriptions. Having experience of unexpected behaviour with dates I had expected that it will be more consistent on the Ninox cloud, because server will not travel thus its timezone will not change. I thought so until now. All of my dates (including dates of birth which is really important because we are managing personal data for clients) shifted by 1 day.
I was shocked, because I couldn't figure out why it happened (knowing why it could happen) and then remembered that I was changing Windows timezone recently. I now confirmed that was the reason it happened (you have to restart browser for it to take effect).
Since I know how to fix all my records, I will be able to continue working with Ninox database. But the issue is that any user with their PC set up to different timezone might see dates with wrong day. This is unexcusable! I believe you HAVE to add a timezone setting in database options or make dates not being affected by change of timezone.
I would really appreaciate to hear your plans how you are planning to fix this issue and expected dates to implement this. I think general answer will not be okay this time, because it is almost a half of a year since I reported this issue.
-
Additionally if I am using today() to assign a date it creates a date 1 day in the past. Actually it is -1 hour, probably because this command is server-side and server is deciding using its timezone.
-
To fix broken dates I created a column with time(date) and then group it. For example as a result I get 00:00, 01:00 and 23:00 groups. Then I select for all records in 01:00 group I apply date(date) mass update and for all records in 23:00 groupd I apply date(date) + 1 mass update.
Although it is fast method, if you have a lot of dates and a lot of tables then you will might have to repeat that many times.
Interesting case was when date(date) + 1 didn't work. It was 28.10.2018 23:00 and after using date(date) + 1 it returned 28.10.2018 23:00. It was happening only with this date and in the end it appeared that that's the date when daylight saving time ends so basically that date has 25 hours and I was adding 24 hours.
-
There is unexpected behaviour with filtering time column. To see 22:00 I had to put in filter 23:00 to 23:05. Server-side timezone issue.
-
As a workaround am using a the timezonedb api to get the timezone offset so I don't have to worry about daylight savings time. I needed to do this for shared views.
https://timezonedb.com/references/convert-time-zone
My formula looks like this:
let response := http("GET", url("http://api.timezonedb.com/v2.1/convert-time-zone?key=YOUR-API-KEY&format=json&from=Europe/Berlin&to=America/New_York&time=" + format('Date / Time', "X")));
'Date / Time' + number(response.result.offset) * 1000
-
Ninox support, I have asked for a reply that would address the issue with approximate dates when it is going to be solved and how you are going to solve this. If you decide not to answer my questions please state so. Thank you.
P.S. Thanks for sharing your workaround Blackie, unfortunately it only can help with previewing dates correctly using separate formula, but the original fields will still be vulnerable to shifts in time, so it does not fully addresses the issue.
-
Hi Admin,
We apologise that we have missed your post form last Wednesday.
If you have urgent issues, could you please send us an e-mail to support@ninoxdb.de. That would help to give you a more direct and quicker support. Thank you very much.
The work according the time zone issue is already in progress and we hope to release the improvement in one of our upcoming versions. Thank you for your patience.
Best, Jörg
-
Hello, any updates on the progress of corrections? Previously in this thread I had proposed some options how I can see it can be corrected. I would really appreciate to hear your speciffic plans on this.
P.S. Please add to improvement list date format verification that is currently based on language/region settings. The reason is that for example Chrome installs with English (US) language and this makes Ninox display dates in mm/dd/yyyy for people that are not living and working in US. Again the same way with dates issue discussed in this thread there should be a setting that admin could use to override for all clients how he wants to show dates OR there should be mandatory initial setup for users with persistent settings over different devices OR some kind of other way to warn user that he might see dates in improper format.
-
As it appears, there still seems to be no fix for this. It's been over a year! I also would really appreciate a defintite ETA for a permanent fix for that!
-
20.11.2020 (( still not fixed. Almost 2 years passed
-
Hello!! Anything new on this?
22/01/2021
-
I have heard that it is to be solved in the next release, stay tuned
-
@blackie
Thank you for sharing your solution. It works for me. I just would like to add one item.
response results on local Mac app and cloud app are different. So it is advised to add somewhere command ninoxApp() and if command result = "server" then add timezonedb response result as time shift in corresponding time units.
-
As mentioned above, Ninox stores ‘date’ field values in UTC format as timestamps. I think this approach is justified. It seems the app interface lacks setting option with the choice of user’s time zone. Hopefully this will be fixed in the nearest release as promised.
As a temporary solution, the following approach might be deployed:
1) take a saved timestamp and add (subtract) a time offset depending on your timezone ( blackie's post with changing offset is very useful);
2) assign the shifted timestamp to a variable to perform the necessary calculations with it;
3) the result of calculation as a new timestamp must be shifted back i.e. subtract (add) the same time offset before storing value in some field.
Sample:
We have given ‘EffectiveDate’ (Date) and ‘AnniversaryNo’ (Number), then we need to find ‘AnniversaryDateTime’ (DateTime).
Code:
let offset := 0;
if ninoxApp() = "server" then
offset := 5
end;
let n := AnniversaryNo;
let effDt := EffectiveDate;
let x := effDt + offset / 24;
tool := text(x);
AnniversaryDateTime := datetime(year(x) + n, month(x), day(x), -offset, 0, 0, 0)
Note:
Since behavior of web app and mac app (or another device app) with timezone issues are different it is advised for those who use cloud as well as device apps to check what app is deployed during current session.
In my case offset is 5 because I live in Asia and timezone difference with Berlin is 5 hours in winter and 4 hours in summer.
Dynamic offset taking into account daylight saving mode might be find in Blackie’s workaround posted above.
-
Any update on this??
-
Same problem.
-
Would be nice to have a proper fix for this as I travel a lot and still need to manage the system and be relevant to people back at the business.
-
Really need a fix or clear solution. Its been 3 years...
HELP!!
-
Still hoping there is a solution to this Daylight Savings issue. 5 Jul 2022
Content aside
-
1
Likes
- 2 yrs agoLast active
- 21Replies
- 6721Views
-
4
Following