2

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?

Community
  • 1
  • 1
Willy
  • 9,848
  • 22
  • 141
  • 284

2 Answers2

2

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.

Rene M.
  • 2,660
  • 15
  • 24
  • Could you indicate me how to do that? What would be the field types? int for unix timestamp and what type for the separated timezone value? Also, for getting timestamp is ok System.currentTimeMillis()? and finally how getting timezone? which function? could you post a snipped code? thx. – Willy Jan 07 '15 at 20:54
  • Also, how to create table with that columns, timestamp and timezone with a default value? – Willy Jan 07 '15 at 20:55
  • Yes you can use System.currentTimeMillis which is always the system time in system default locale (timezone). When create a Date object it is also set to current time and the timezone is set too. – Rene M. Jan 07 '15 at 20:59
  • Ok, but I need to columns in my table right? one of type long to store timestamp and another for timezone? which type for timezone column? – Willy Jan 07 '15 at 21:20
2

Sqlite By default save datetime in the utc format yyyy-mm-dd hh:mm:ss, You can save the value in datetime type column as current_timestamp for current time. You can get the datetime in the local time zone as querying-

  datetime('now','localtime');// for current time
Sanjeet A
  • 5,171
  • 3
  • 23
  • 40