I think, I have got the correct timestamp after torture and help from previous answer. It's a timestamp generated on Comfort panel logging data in RDB file, a SQLite database. I have used a program named:  DBPlot 

SELECT VarName,TimeString,strftime(VarValue),strftime(Validity), datetime((Time_ms/1000000)+julianday('1900-01-01')) FROM main.logdata 

Old thread, but still the same problem.

A customer is not happy with standard alarmhistory object. So I made a new one with scripting and SQL (Data is logged to SQL server)

But I need to be able to filter out the records inbetween certain dates.

So I have a beginning and end date field, but how do I translate those 2 date/time fields to Time_ms format??

Thx

And now a solution :-)

In fact, the given time in miliseconds is not correct.

It's time in numberformat for excel.

When you divide the ms time given by the logoutput by 1000000, then you have the time in DAYS counting from 1st Januari 1900.

A day for me has 86400000 miliseconds, maybe for some people days are shorter or longer...

Here a function (in visual basic)  to calculate the ms to a datetime format.

I use this with a visual basic application that I am making to visualize .rdb files, that are  SQLite files.

I convert here the time_ms field, to a new datetime (in memory) field to make it more usable.

The SQLite doesn't support the datetimefield type.

I've been testing and trying and I already found this for the WinCC flex scripting:

>>>

Dim DateJan1st1900, Diff
DateJan1st1900  = CDate("1900-01-01 00:00")

Diff = CStr(DateDiff("d",DateJan1st1900, CDate(SmartTags("BeginDT"))))

<<<

-> BeginDT is internal tag from WinCC and in DateTime format

if I then multiply diff with 1000000, then I should get the time_ms value for midnight, but it doesn't:

1) the nr before the comma is always 2 off with the time_ms value

2) the time_ms is in greenwich time (1/1/1970 0:0:0 gives 25569041666.6667 and 1/24 = 0.041666667)

2) is easy to solve, but I wonder where 1) comes from. if I use online calculators they give me the same result as my calculation...

Or it would be that 1/1/1900 + current day is added with Siemens and not in my calculation.

So for the moment, I add 2.041666667 to my calculation to make it work.

Accordingly to my answer the sqlite datetime format at is: datetime(Time_ms/100000+ julianday(1990/1/1))

julianday- the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).

So the arbitrary datetime format from seconds.miliseconds is : seconds_to_datetime(Time_ms/100000+ date_to_seconds(1990/1/1))

And you ask for Time_ms in Siemens format from DT:

Time_ms/100000 =  datetime_to_seconds(DT_Stamp)- date_to_seconds(1990/1/1)

Time_ms=  100000 * (datetime_to_seconds(DT_Stamp)- date_to_seconds(1990/1/1))

But if you ask for time difference, then it's more trivial. No matter what is system offset:

T_diff = (A+Day_X) - (B+Day_X) = A-B

So, all you need is convert from DT to seconds.miliseconds and then multiply with 100000.

Siemens doesn't uses Julien Date (1900-1-1) but the Dublin Julian Date (DJD) 1899-12-31.

If you do the convertion with the Julien Date you will have 2 days of difference.

Look this Post:

https://support.industry.siemens.com/tf//ww/en/posts/time-ms-decoding/200431?page=0&pageSize=10