Importing raw UNIX time – [SOLVED]

[samParticipant]

ISSUE SOLVED / QUESTION ANSWERED – see reply post below.

————–

Hello DG team —

I have a .csv import, one of whose fields contains a raw UNIX date/timestamp, i.e. the time in milliseconds since 1/1/1970 UNIX epoch.

An example of just this column taken from the .csv file:

1709931621652
1709931622650
1709931623141

I like having timestamp data in this format as it is absolute and unambiguous.

How can I import the raw UNIX time into a column in DG, and then have DG format that raw UNIX time for human-readable display?

That is, I want to keep the UNIX time in milliseconds in my data, but have DataGraph reformat it according to the ICU string of my preference.

I am having trouble figuring out if or how there is an ICU format string to handle this. It seems like the ICU-format function in DG is designed to translate any other conceivable format BACK into raw UNIX time (for use within DG internally,) — but not to import raw UNIX time & then display it as one pleases within DG.

• This topic was modified 8 months, 2 weeks ago by sam.
• This topic was modified 8 months, 2 weeks ago by sam.

[samParticipant]

I figured out the problem —

My data is indeed in UNIX time format, but as the number of milliseconds (a 13-digit number), whereas the canonical UNIX time is only the number of seconds since 1/1/1970.

I left the raw milliseconds import column in number format and used a Function field to divide it by 1000 (converting milliseconds to seconds) and then ICU-format that according to my wish.

Works like a charm as it keeps the milliseconds in the datestamp which I need for my application.

The full ICU format string I used is: M/d/y h:mm:ss.SSS aa

(Since it was cut off in the screenshot below)

 

[peter muellerParticipant]

I would suggest for that issue to implement a second date and time Method.

It’s called ISO 8601

Data elements and interchange formats – Information interchange – Representation of dates and times

https://www.iso.org/iso-8601-date-and-time-format.html

That format is used by Banks and large Organization.

That format does not have limitation as 2039 or 1970. The Unix format should not be removed for a few years. But the second format will ease all trouble to an official Standard.

 

[Author]

Posts