Android Question Best way to store exact time in SQLite

[Carl Peters]

Hi,
I am new to Basic4Android.
I am creating a program that needs to store data in sqlite along with an exact date and time. It is very important daylight saving doesn't affect the date and time recorded (ie I want something like UTC time).
Basically, I want to be able to calculate the exact time between two recorded values without "time changes" affecting the calculations.
I believe datetime.now returns seconds since some date in 1970. Is this the best way to record the date and time for my purpose and what data type should I use in sqlite to record the returned 'long' value.

Many thanks.

 

[eps]

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

 

[MaFu]

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

 

[eps]

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

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

 

[nwhitfield]

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

Dim now As Long
now = (DateTime.now/1000) - timeOffset

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.

 

[MaFu]

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

Why not?

 

[eps]

Why not?

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/

 

[MaFu]

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