Record dates change when switching time zones

[Jim Burgess]

When traveling cross-country and switching from Eastern time zone to Pacific timezone, the dates of all records in all forms on all devices are changed to one day earlier.

This issue has happened both times I have travelled from the east coast to the west coast in the past six months.

[Brendan]

Hi Jim,

Tap Forms stores dates in Unix Epoch format. That means it stores the number of seconds from January 1, 1970 00:00:00 until the selected date that you stored. So the date that’s stored is stored as it was in the time zone you were in at the time. If you change the time zone, the dates will adjust accordingly to accommodate. But that’s not something I’m doing explicitly. It happens automatically by the operating system.

Thanks!

Brendan

[Jim Burgess]

Respectfully, I disagree with you.

First, to clarify, the fields in question are defined in TapForms as “Date Only”, i.e., there is no time component.

As such, the values entered into the date field should be treated as constants. Consider the following cases:

1. I have a form where I record birth dates as one of the fields. A person’s birth date should remain the same regardless of where I am in the world. But your code causes a person’s birth date to change. This should not happen and is a data integrity issue within your app.

2. I have a form that contains follow-up dates and I depend on the contents of that form to provide me with a list of follow-ups I need to do on a particular day. But if I am traveling in a different time zone, those dates could be incorrect causing me to miss a follow-up appointment. Again, a data integrity issue within your app.

Regarding your statement “But that’s not something I’m doing explicitly. It happens automatically by the operating system.” I’ve observed the following in the database:

For a record created on 6/4/14 in EDT the Date field contains 1401854400
This query: SELECT datetime(1401854400, ‘unixepoch’);
Gives this result: 2014-06-04 04:00:00 (i.e., UTC 00:00:00 – 4 Hrs)
So the contents of the field has a time component even though the field is defined as a “date-only” field.
Now when the record is displayed within your app, if this query is used:
SELECT datetime(1401854400, ‘unixepoch’,’localtime’);
the result is: 2014-06-03 21:00:00
The date has changed! Which is the issue I’m reporting. This shows that a likely cause of the problem is that the ‘localtime’ modifier has been applied twice– once when the field value is defined and again when the field value is accessed.

Other entries in the database for records created in other time zones show similar behavior.

For date-only fields the time should not have any influence on the date, but it does and consequently causes a data-integrity issue. A possible solution would be that UTC was always used as the time, and no local time adjustments were applied when accessing the field.

[Brendan]

Hi Jim,

I agree with you that this is a problem. The Unix epoch date is not really a date though. It’s a duration. It’s the number of seconds which have passed since January 1, 1970. And since that’s the case, there is no timezone representation embedded within the values. Therefore, the values naturally have a time component, even if they are set to be Date or Time field types. The difference between Date, Date & Time, and Time is only in the way they are displayed on screen. You can freely switch between them and you’ll then see the time portion appear or the date portion appear.

I’ll have to do some investigations to see what the best way of handling this is. I may have to switch to storing dates in a different way to prevent this issue. I’m really sorry for the trouble this is causing you.

Thanks,

Brendan

[Jim Burgess]

I’m likely oversimplifying this, but it seems…

If the iOS equivalent of this query is used to populate the form’s date field in the table:
select strftime(‘%s’,’2014-07-02′);
(where ‘2014-07-02’ is the string representation of the date returned by the uiDatePicker)
then an iOS equivalent of this query:
select date(1404259200,’unixepoch’);
(where 1404259200 is the value in the table’s date field)
would return the proper date.
Note that the ‘localtime’ modifier cannot be used in either query.

I also know that things are always more complicated inside the actual code, so this approach may not be feasible.

In any case, thank you for your consideration, and for all your efforts with TapForms.

[Author]

Posts