When I go to my Airnote dashboard, and click the download button, it generates a CSV file.
The file used to have a date and time for each datapoint. Now I see a created column, which seems to be when the sensor data was collected, but it’s a large integer instead of a date/time value.
That is, I see 1728485923 but I’m expecting to see something like 2024-10-09 10:59:23 or something similar.
Hi Greg,
Sorry for the inconvenience this has caused you when viewing the CSVs. We recently changed the API endpoint we’re using to pull Airnote data from Notehub, and in the new API all timestamps are in epoch time instead of formatted timestamps.
I can throw a ticket in the backlog to see about transforming the timestamps before the data is downloaded, but in the meantime, a time converter like https://www.epochconverter.com/ may be of some use.
The value of the created column is in Unix Epoch seconds. That is, it is the number of seconds passed since 1 January 1970, UTC
To convert this integer value, you can use different functions depending on what analysis tool you are using.
For Microsoft Excel, you need to convert this to an Excel date format
=(A1/86400)+DATE(1970,1,1)
Excel Dates use fractional days, and begin at 0 January 1900. (Yep, 1 January 1900 has the value of 1, so 1 day has passed at the beginning of the first day of January 1900 in Excel dates.)
Since the Unix Epoch begins at 1 January 1970, the 0 value must be shifted to this date instead of 1 January 1900
The value will be in the UTC time zone. If you need to correct this value to local time, you need to add or subtract the UTC offset for the local time zone for that date and time (daylight savings makes this trickier). You may way to look into custom functions to support this.
The same formula used in Excel can be used in Google Sheets.
However, Google Sheets added a function to compute the timestamp from the Unix Epoch
=EPOCHTODATE(A1,1)
This value will be in the UTC time zone like Excel. If you need to correct this value to local time, you need to add or subtract the UTC offset for the local time zone for that date and time (daylight savings makes this trickier). You may be able to do this with custom Javascript style functions for your Google Sheet.
If your data is imported as a table object, and the table is named T
T.created = datetime(T.created, ConvertFrom='posix', TimeZone='UTC');
This will perform all of the necessary conversions, and apply a time zone to the resulting object. This makes converting to a local time zone much easier.
You can choose to omit the TimeZone argument if you don’t want to set a time zone.