Raw time data generated by System Db
I have been unable to format/convert the raw data of the time stamps in the System Database to a formatted time in Excel for reporting purposes. The numbers in the System Db fields are very different from the raw time data Excel uses. For example, here is a date in the System Db: 637837262515643000, and here is a time in Excel: 44648.6657070602. How can I take the System Db values and display them in formatted in date/time formats in Excel?
-
Here are two screenshots to illustrate my point. The first is a report generated in History Explorer, the second is that same report exported into Excel. The Excel time field defies all attempts at formatting.
0 -
Changing the Export Mode from the Value to Text works for me
0 -
Thanks Peter. Unfortunately that doesn't solve my issue. My use of the History Explorer was intended only to illustrate the issue, but what I'm actually doing is pulling the data via SQL from the database into a pivot table. I need to be able to manipulate these dates so I can group by the print date and report on the quantity of each document that was printed within a selected date range. To do this, I need to be able to convert the date values that the System Db stores into Microsoft's date-serial value. Microsoft starts day one at January 1st, 1900, and adds decimals after that for the time. I need to understand how Seagull is serializing their date values, which is clearly different from how Microsoft does it, so that I can write a formula that converts the Seagull date-serials into Microsoft date-serials. Seagull is not using a date/time data type in the System database, you are using a bigint datatype instead. Can you help?
0 -
I think I figured it out. It looks like the time is stored in UTC seconds, starting with 01/01/01. Now I need to find a way to convert it into a datetime.
0 -
For anyone else encountering this problem, I can confirm that this formula in Excel will convert the Seagull dates stored in UTC seconds from 01/01/01 to the current Microsoft date-serial for UTC time. You'll also need to make an additional adjustment for your time zone and daylight savings time.
=(LEFT([Seagull Time Field],11)-59926780800)/86400
Note that 59926780800 is the number of seconds from 01/01/01 to Microsoft's start date of 01/01/1900.
0
Vous devez vous connecter pour laisser un commentaire.
Commentaires
5 commentaires