Search code examples
sqlitetimestampunix-timestampntpdatetime-conversion

18 digit timestamp to Local Time

I came across Sq-lite database.I found our date time is stored in 18 digit time-stamp.

Please help me how to convert it to Local Time. (I tried to convert it(No.of milliseconds from 1900). But i did not get it.It shows 29 days difference. I added 29 days of milliseconds to time. But time output is wrong.My assumption is, it will be NTP time.)

Here i give 18-digit time-stamp and its equivalent date time.

Time-Stamp Date-Time

362087070028927400 27-10-2014 15:06:57
362087302762879170 27-10-2014 15:22:06
362087302763135170 27-10-2014 15:22:06
362087302851460030 27-10-2014 15:22:06
362087302851716030 27-10-2014 15:22:06
362087302851716030 27-10-2014 15:22:06
362087305419799000 27-10-2014 15:22:16
362087307972777150 27-10-2014 15:22:26
362087310530875300 27-10-2014 15:22:36
362087313092045760 27-10-2014 15:22:46
362087315652448200 27-10-2014 15:22:56
362087318210802600 27-10-2014 15:23:06
362087320772741060 27-10-2014 15:23:16
362087323333655740 27-10-2014 15:23:26

Thanks

Solution

  • It looks to me like it's based at 1970 (the Unix epoch) instead of 1900, with 256000000 "ticks" per second.

    I haven't seen such a format before, but it seems to check out - for example, using Noda Time:

    using System;
using NodaTime;

class Test
{
    static void Main()
    {
        Console.WriteLine(ToInstant(362087323333655740));
    }

    static Instant ToInstant(long value)
    {
        // We don't have to truncate to milliseconds, but it's simple for now...
        long millis = value / 256000;
        return Instant.FromMillisecondsSinceUnixEpoch(millis);
    }
}

    Output:

    2014-10-27T09:53:26Z

    ... which would correspond to 2014-10-27T15:23:26 as the local time in India.