CODEWITHSUNDEEP
sqlitedatedatetimetimestamp
ayon
ayon

Reputation: 2180

Timestamp conversion procedure

I am stuck with a Date or Timestamp conversion in the SQLite database. I have an SQLite database in which I have a column with BIGINT data where some kind of timestamp is kept. These values are 18 digits, some examples are 634741056000000000, 634766976000000000. These values have come to the database from a windows app from the datetimes like the example. Some equivalent Date to that timestamp is given below.

  1. 2012-06-01 00:00:00.000 = 634741056000000000
  2. 2012-07-01 00:00:00.000 = 634766976000000000
  3. 2011-11-01 00:00:00.000 = 634557024000000000
  4. 2011-12-01 00:00:00.000 = 634582944000000000

This is all I know, I have tried several possible conversion procedures but no luck yet. Can anyone help me out with a conversion procedure? I am using this in an iOS application, so just the conversion procedure will me a lot. Any help will be highly appreciated.

Upvotes: 0

Views: 215

Answers (1)

CL.
CL.

Reputation: 180070

When we compare the length of a month:

> SELECT strftime('%s', '2012-07-01') - strftime('%s', '2012-06-01'),
  634766976000000000 - 634741056000000000;
2592000|25920000000000

we see that the unit is one 10000000th of a second.

To compute the epoch, just use the difference between these values and the SQLite's unixepoch values:

> SELECT datetime(strftime('%s', '2012-06-01') - 634741056000000000 / 10000000,
                  'unixepoch');
0001-01-01 00:00:00

Upvotes: 1

Related Questions