Nokia Symbian 3 date and time database format

Can you provide a bit more info? where did you get that number from? did you hack it out of a hex dump - was big little endian? was it taken from a sqlite database…..

IIRC (away from the office) Nokia used to use a nibble swapped format to store the data as essentially BCD - see the article a brief history of time stamps on my website.

www.sandersonforensics.com

The number represents the number of 100 nanosecond intervals since 01/01/0001 000000 (the number is represented as INT64).

I'm in the process of writing a formula for Excel )

Excel is dodgy when it comes to dates before 1900. In fact even 1900 might be problematic (it wasn't a leap year). Also don't forget about the switch to the Gregorian calendar.

We use something similar to the following in Python

datetime(1, 1, 1) + timedelta(days=-378, microseconds=n)

Excel is dodgy when it comes to dates before 1900. In fact even 1900 might be problematic (it wasn't a leap year). Also don't forget about the switch to the Gregorian calendar.

Sure D , those are known issues that prevented historians from dating accurately all the SMS's retrieved from the Nokia's used in the Boer Wars with Excel. 😯

For NO apparent reason, check how one can shoot oneself in the foot programmatically (in Python)
http//www.toodarkpark.org/computers/humor/shoot-self-in-foot.html
wink

jaclaz

Can you provide a bit more info? where did you get that number from? did you hack it out of a hex dump - was big little endian? was it taken from a sqlite database…..

IIRC (away from the office) Nokia used to use a nibble swapped format to store the data as essentially BCD - see the article a brief history of time stamps on my website.

www.sandersonforensics.com

Paul,

Its an Int64 number obtained from the messages database file from a Nokia N8. I not having much success with an excel function (partly because I am feeling under the weather -(

I was wondering if the number has to converted initially that you can use the formula to calculate the number of nanoseconds from either 1/1/0001 or 1/1/1601.

Any thoughts will be most welcome?

Neil

Its an Int64 number obtained from the messages database file from a Nokia N8. I not having much success with an excel function (partly because I am feeling under the weather -(

Produce another "checked" couple like this one
63518562987097375 which is translated to 15/10/2012 141613 (UTC)

I was wondering if the number has to converted initially that you can use the formula to calculate the number of nanoseconds from either 1/1/0001 or 1/1/1601.

Any thoughts will be most welcome?

From that single data, the number correspondent to 1/1/2000 000000 is seemingly 63.114.937.214*10^6.

Take this
63.518.562.987.097.375 (in excel it will likely become 63.518.562.987.097.300)
divide it by 1,000,000
get 63.518.562.987
divide this by 86400 (24*60*60)
get 735.169
divide by 365 (approximated) and you get 2014. (which is close enough to 2012 to believe that the original info is "wrong" and the number actually represents nanoseconds and not hundreds of nanoseconds)

Now in excel, subtract from the date 15/10/2012 141613 the date 01/01/2000 141613.
You will get an integer number of days, 4671.
Now
4671*86400+14*3600+16*60+13= 403.625.773
63.518.562.987-403.625.773= 63.114.937.214

Check other dates calculating the time starting from 1/1/2000 000000.

jaclaz

Its an Int64 number obtained from the messages database file from a Nokia N8. I not having much success with an excel function (partly because I am feeling under the weather -(

Produce another "checked" couple like this one
63518562987097375 which is translated to 15/10/2012 141613 (UTC)

I was wondering if the number has to converted initially that you can use the formula to calculate the number of nanoseconds from either 1/1/0001 or 1/1/1601.

Any thoughts will be most welcome?

From that single data, the number correspondent to 1/1/2000 000000 is seemingly 63.114.937.214*10^6.

Take this
63.518.562.987.097.375 (in excel it will likely become 63.518.562.987.097.300)
divide it by 1,000,000
get 63.518.562.987
divide this by 86400 (24*60*60)
get 735.169
divide by 365 (approximated) and you get 2014. (which is close enough to 2012 to believe that the original info is "wrong" and the number actually represents nanoseconds and not hundreds of nanoseconds)

Now in excel, subtract from the date 15/10/2012 141613 the date 01/01/2000 141613.
You will get an integer number of days, 4671.
Now
4671*86400+14*3600+16*60+13= 403.625.773
63.518.562.987-403.625.773= 63.114.937.214

Check other dates calculating the time starting from 1/1/2000 000000.

jaclaz

How do you convert the long string to a date (i.e. 63.114.937.214 to 1/1/2000 000000)?

How do you convert the long string to a date (i.e. 63.114.937.214 to 1/1/2000 000000)?

I don't. 😯

With the given info (to be checked against more dates) I determined that between the 15/10/2012 141613 and 1/1/2000 000000 403.625.773 seconds elapsed and that consequently the "number" corresponding to 1/1/2000 000000 is 63.114.937.214.
The date 1/1/2000 000000 is (in Excel serial date number) exactly 36.526,00

So you get that the number you get (divided by 1 million) minus 63.114.937.214 is the number of seconds since 1/1/2000 000000.
Provided that you have another date number of (example)
63.518.731.478.212.125
you divide it by 1,000,000 and obtain
63.518.731.478
Now
63.518.731.478-63.114.937.214= 403.794.264 (seconds since 1/1/2000 000000)
403.794.264/86400= 4.673,544721096090 (days since 1/1/2000 000000 in Excel serial)

(36526)+(63.518.731.478-63.114.937.214)/86400= 41.199,544721096100
which, when the cell is formatted to "date format" becomes
17/10/2012 13.04.24

If you prefer you can use Excel functions, say that you have the above serial in D36
=YEAR(D36) -> 2012
=MONTH(D36) -> 10
=DAY(D36) -> 17
=HOUR(D36) -> 13
=MINUTE(D36) -> 04
=SECOND(D36) -> 24

Of course setting the "start date" to 1/1/2000 000000 is arbitrary, but it should avoid the issues with gregorian calendar and the "queer" 1900 leap year polar mentioned.

And you can still add the 6 rightmost figures of the original number as "nanoseconds".

jaclaz

The penny has dropped!!!

Thanks for help Jaclaz

Neil