Android Question Best way to store exact time in SQLite

This? http://www.b4x.com/android/forum/threads/utc-file-time.30165/#post-175327

Only five datatypes exists in SQlite: NULL, INTEGER, REAL, TEXT, BLOB
Therefore use INTEGER.

MaFu said:

Only five datatypes exists in SQlite: NULL, INTEGER, REAL, TEXT, BLOB
Therefore use INTEGER.

Click to expand...

Do NOT use INTEGER, this will only cause you grief.

The value returned is milliseconds, and will obviously depend on the device being set correctly (the user might have the time zone wrong, anyway, and may or may not have the time and zone set to auto update based on the mobile network).

I use an integer field in SQLite, and store the number of seconds, rather than milliseconds, eg


timeOffset is an Int, which I calculate myself, rather than relying on the built in functions. I can easily do that because my app calls a server, which is set to use NTP, runs on UTC all the time, and returns the current server timestamp as part of the results of every API call. So, I simply calculate the exact time offset of every device, relative to the server and use it to store all data in the database as UTC.

eps said:

Do NOT use INTEGER, this will only cause you grief.

Click to expand...

Why not?

MaFu said:

Why not?

Click to expand...

Actually... Sorry I was thinking in terms of Oracle and SQL Server and storing Date and Time.

If storing in milliseconds, then Integer should be used.

Set the Timezone to 0 and it will be UTC - as per the link I posted.

It's a little unclear in the OP, what they are storing and exactly what they are using the stored information for... So two INTs could be used and pumped in to DateUtils.

http://www.b4x.com/android/forum/threads/dateutils-simplifies-date-and-time-calcuations.26290/

Because in SQlite no date or time format exist, it's the best practice to store the data as long in an INTEGER field.