How does InfraMarker handle dates? – Berntsen International

InfraMarker is a worldwide Cloud application. In order to support people collaborating across the world, any date or datetime is recorded from the user in their local time zone, and then stored on the server as UTC.

UTC is the Coordinated Universal Time. It is the time that all clocks in the world are set by. For more information see the wikipedia entry here: http://en.wikipedia.org/wiki/Coordinated_Universal_Time

Then when anyone views the data on their client, it will be converted to their local time zone so that it will make sense to them. A person in New York will see a different time than a person in California.

Wikipedia also has a list of timezones and their offsets from UTC:

http://en.wikipedia.org/wiki/List_of_UTC_time_offsets

For example, to convert from UTC to the Pacific Standard Time (PST), you can see that PST is UTC-8:00, which means when it is 4:00 p.m. in UTC, it will be 8:00 a.m. in PST. This makes sense as PST is east of Prime Meridian so it will have an earlier time.

Exporting data

This can create some confusion when exporting data from InfraMarker. For example, you can go to the advanced queries and do a simple query for the name of a record and the data it was created (provided your dataset has those fields)

Dates as viewed through the InfraMarker web UI. Because I am in San Francisco, I see the dates in the pacific standard time zone (PST)

We then save the query, and go to the dataset list and export it to Microsoft Excel's Format (xlsx).

Reimporting that excel spreadsheet into a spreadsheet editor (in this case Google Docs) you can see that the dates's do not match:

Knowing that the PST timezone is defined as UTC-8:00, we can follow some simple steps to convert the date to our local time zone.

Convert before exporting

You can also use SQL to do a convert by subtracting the time using a query to subtract a time interval of 8 hours. You can find details about PostgreSQL's date and time functions from their help documentation.

An example query is here:

select name, date_created, (date_created - INTERVAL '8 hours') as date_as_pst from dataset_12617 order by date_created desc

The resulting PST Date will seem wrong in the ui as InfraMarker believes it to be a UTC date and it will be converted to PST as you are looking at it.

Date_created as PST and date_as_pst as PST - 8

However when you open up the exported file you will see the date as you were expecting:

date_created as UTC, date_as_pst as PST

Converting after exporting in Google Sheets

First, create a new column and add the formula =[DateColumn]-( (1/24) * 8)

This formula works for google sheets because each day is represented behind the scene as a number fro 0-1. If you want to subtract 8 hours, simply multiply 1/24 by 8.

Formula to convert dates from UTC to PST

Then use that formula to define the dates for all the other rows:

Dates are in UTC and PST

Converting after exporting in Microsoft Excel

Microsoft excel works the same way. The formula is exactly the same as well.

Add a column and use the same formula

Dates are now converted to PST

Sending and retrieving data directly from the server

As stated before, the client (website, mobile app, etc.) will be the one in charge of converting the data from UTC to local time and viceversa. This, however, is not the case when sending and retrieving data directly to the server.

Whether you are using the SQL API or the SubmitChange endpoint from the Python or Javascript library, or directly from the REST API, you will be the one in charge of doing the time zone conversions. The server will always store and expose all datetimes as UTC.

Sending data to the server

When sending datetime values to the server, remember to convert them to UTC in advance. Even if you add the time zone information, the server will not take them into consideration. So, for example, sending "2015-06-16 15:07:34", "2015-06-16 15:07:34 -08:00" or "2015-06-16 15:07:34 PST" will result in the same value stored in the server: "2015-06-16 15:07:34 UTC". We plan to add support for datetimes with time zone information in the future.

Retrieving data from the server

When retrieving datetime values from the server, remember to convert them back to your local time zone.

Useful operations with datetimes in Python

Get current time in UTC string (ready to be sent to server):

import datetime
utc_string = datetime.utcnow().isoformat()

Convert datetime to UTC string (ready to be sent to server):

from pytz import utc
utc_string = dt.astimezone(utc).isoformat()

Parse UTC string from server to datetime with timezone:

from dateutil import parser
from pytz import timezone
pst = timezone('US/Pacific')
dt = parser.parse(utc_string).astimezone(pst)

Useful operations with datetimes in Javascript

Get current time in UTC string (ready to be send to server):

var utcString = (new Date()).toISOString()

Convert datetime to UTC string (ready to be send to server):

var utcString = dt.toISOString()

Parse UTC string from server to datetime with timezone:

var dt = new Date(utcString);
dt.setMinutes(dt.getMinutes() - dt.getTimezoneOffset())