Store date & time in SQLite database in Android

Question

I am a bit confusing about how to store date $ time in SQLite database in Android. I need to store current date & time in my database table.

so:

1) Which type to use when creating date/time column in my table? 2) How to get current date/time and store it into database table according to the column type chosen?

Regarding point 1) some people says to use a text type, some others to int and some others to datetime..... but SQLite 3 recommends storing date/time as below:

SQLite 3 documentation

I cannot see that SQLite supports datetime type. The only types supported by SQLite are: null, integer, real, text and blob (so datetime is not listed here).

so I do not understand why here

it says to use DATETIME as column type and set default value as DEFAULT CURRENT_TIMESTAMP when creating database table.

Storing it as milliseconds, the problem is that timezone is lost. In this case it is said to save that separately and I do not kwnow how to get rid of this...

Regarding point 2), I think of several ways.... using DATETIME('now') or System.currentTimeMillis()... but I do not know if it is the best way....

How can I get rid of this?

Answer 1

The documentation at https://www.sqlite.org/datatype3.html 1.2 Date and Time Datatype says that depending of the usage of the date and time functions of sqlite it stores date and time values in several kind of field types. They types you explained.

Also the docs say at https://www.sqlite.org/lang_datefunc.html you can use the DATETIME Function to create a date time string value. Also including the timezone.

Hope that helps. I would prefer to use unix timestamp and seprated timezone value. Because you can then create the date object easer.