How to convert the rawdate column to y-M-d h:m:s?

[petercreateParticipant]

As it is

Date should be 2024/5/9. Digitals should be hour, minute, second, …

[Jean-Yves Le StangParticipant]

Hi,

Your RawDate column is a number column, so it cannot figure-out a date, only a number.

When you insert a date column in DG, two different formats must be considered:

The first one is the input format, which is that of the imported file, or that you have to match when you key-in a date. If there is no match, DG will not recognise the date and show it in red as in line 2.

The second one is the display format, which DG will use to display the value in the table.

So to show the Date in the  “y-M-d H:m:s” format you can proceed as follows:

• Either you create an expression column from Date (here called “Formatted date”), select the type to be a date and give it the convenient ICU display format – this is the “long way”.
• Or you give the Date column directly the suitable display format (see Date2 column which is a copy/paste of the Date column but shows the Date data directly with the format you have selected. This is the “short way” to do it, but data will anyway have to be inserted in the input format (displayed only in a selected cell).

If you give the same expression column a number format (refer to RawDate column), then you get the RawDate value which is the time in seconds since 1st January 1970 (see line 5).

I hope this is helpful.

[dgteamModerator]

Nice explanation of the date format and how the date in DataGraph shows the number of seconds since 1970 when displayed as a raw number. This is a standard called the UNIX time.

https://en.wikipedia.org/wiki/Unix_time

@petercreate The number you are showing seems to be the format used by Excel, which is days since 1900. If you converted this to the UNIX format, you could use an expression column to display it as a date.

For example, here is a screenshot from Excel to first convert the raw time there.

https://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp

Then, in DataGraph this number will work …

Is there a reason you have to use the Excel date/time format? We have seen some issues with leap years not being correctly handled, and you have to be careful that the values are not rounded. In general, we would recommend avoiding the Excel raw date format.

One option that might help, if you have the individual components of the date/time, the “seconds” function can combine them into one column that provides the UNIX time. Then, it can be displayed as the standard day and time.

seconds(year,month,day,hour,min,sec)

Here’s an example showing as either seconds or date/time.

Or you can combine the date and the time …

[spochtsfroindParticipant]

The integer part is the number of days since 1900-01-01, the fractional part is the normalized fraction of seconds per day. Due to a bug in some excel-versions these numbers have an offset of 2 days so you have to count from 1899-12-30.

In DataGraph you can do this:
– set the RawDate column type to “Date”
– set “Type” to “Time since”
– set “Time in” to “Days”
– set “From” to 1899-12-30 00:00:00
– set “Display” to your liking ie “y/m/d”

Hope this helps.

[Jean-Yves Le StangParticipant]

@petercreate

Many thanks for additional information regarding date conversions in Excel. Another precaution is to check in the calculation preference tab of Excel whether it is 1900 or 1904 that is used as reference for the calendar… and check/ uncheck/ convert accordingly.

[tom lawtonParticipant]

Off-topic, but does anyone know the reason for the 1904 datum? 1/1/1904 is also used by LabVIEW. Guess I should ask ChatGPT why… 😀

-and here’s what it said:

LabVIEW uses January 1, 1904, as its date datum for historical and technical reasons.

Compatibility: One reason is historical compatibility with Macintosh systems. The Macintosh operating system used January 1, 1904, as its date epoch. LabVIEW was initially developed on the Macintosh platform, and this choice of epoch was carried over into LabVIEW for consistency with the operating system.
Avoiding Year 1900: Another reason is to avoid the year 1900, which is commonly used as the date epoch in many other programming languages and systems. The year 1900 was not a leap year according to the Gregorian calendar, but it was treated as one in early implementations of some programming languages like BASIC. This discrepancy caused issues with date calculations. By choosing 1904, LabVIEW avoids these potential pitfalls.
64-Bit Time Stamps: Additionally, using January 1, 1904, as the date epoch allows LabVIEW to represent time stamps as 64-bit integers with microseconds resolution without running into the year 2038 problem. This problem arises when using a 32-bit signed integer to represent time as the number of seconds since the epoch. By choosing 1904, LabVIEW extends the range of representable dates into the future.
Overall, the choice of January 1, 1904, as the date datum in LabVIEW is a combination of historical precedent, technical considerations, and compatibility with other systems.

Now we know…

• This reply was modified 6 months, 1 week ago by tom lawton.

[petercreateParticipant]

@ spochtsfroind, this reply hits the target. But thanks to all other replies, too. All are inspiring.

[Author]

Posts