1

I´ve an IoT architecture composed of remote units that collects data from several sensors and stores then locally using a sqlite3 database. From time to time these data are uploaded to a cloud server. In the cloud server there is a web application that shows logs and results from all the uploaded data.

So far, so good I need to handle datetimes and timezones correctly to avoid information mixing and guarantee correct reconstruction of data on time. Since now I´ve been handling data using only UTC datetime, but now I need to build a way to handle timezones. I´m using this post as a general guideline.

My remote unit runs linux embedded and my remote software is build using C++. So every time I need to get datetime I´m calling std::chrono::system_clock::now(), that is returing UTC time no matter what time zone is set (this tought is considered true based on this answer from another question of mine.).

For today my local and remote databases stores only a single value being seconds_since_epoch converted to milliseconds with the following code:

int64_t TimePointToDb(const std::chrono::time_point<std::chrono::system_clock> &time)
{
    auto count = time.time_since_epoch();
    auto value = std::chrono::duration_cast<std::chrono::milliseconds>(count);
            return value.count();
}

The returned value is stored on sqlite3 field (all sqlite3 fields are strings) and then uploaded to mysql bigint format.

At the cloud server, built on PHP, this bigint is then converted to human readable format using:

$dt = new DateTime();
$dt->setTimeStamp(value);
$dt->format("Y-m-d H:i:s");

where value is the stored bigint value.

For now, no timezones or timeoffsets are handled, and this is where I need help.

As far as I understand I need to create a new database field on both local sqlite3 and cloud mysql to store the timeoffset (not the timezone, as the daylight may change timezone).

So my questions are:

  • a) Is that the "best practice" or "recommended" approach to handlethe timezone. Is my strategy valid ?

  • b) In C++, how can I get the system´s current timeoffset ?

  • c) What is the format to store this timeoffset in both sqlite3 and mysql ? Continue using the unix datetime ? How to convert the timezone to the unix datetime ?

  • d) In the cloud, how PHP can show the correct time, now considering the time offset ?

Thanks for helping.

Community
  • 1
  • 1
Mendes
  • 17,489
  • 35
  • 150
  • 263
  • I've updated http://stackoverflow.com/a/39274464/576911 for b). – Howard Hinnant Sep 01 '16 at 16:10
  • Where exactly do you need time offsets? I personally would prefer to continue storing all data in UTC and delay conversion until having to show data in the web app. – Aconcagua Sep 01 '16 at 16:24
  • If it is for the local user, maybe you'd convert time even at the user's browser with java script? You should have easy access to users local time zone there... – Aconcagua Sep 01 '16 at 16:25
  • Aconcagua, The date/time that needs to be shown is the date/time at the moment of the data collection. What is the timezone at the moment of the data collection ? – Mendes Sep 01 '16 at 16:52
  • I'll write a more complete answer when I can, but what you need is called a `datetimeoffset` in many languages. – Matt Johnson-Pint Sep 01 '16 at 19:08

1 Answers1

1

The "best practice" is to always store everywhere UTC times. These timestamps need to be converted to local time only in user interface. That is, on your web pages, app window dispaying data, etc. so the time zone is used only when displaying, not when storing time.

BJovke
  • 1,737
  • 15
  • 18