Geek Answers Handbook

Format integer to formatted date in select SQLite expression

I have a SQLite database in an Android app that stores dates as integers. These integers are derived from the call Java.util.Date.getTime();. I am trying to run a raw query of my database to get the cursor to go to the CursorAdapter and display in the ListView, but the date is stored as an integer returned getTime().

To keep my program simple, I would like to avoid using the SimpleArrayAdapter and stick with the CursorAdapter.

Somehow you can format the integer in the date mm-dd-yyyycolumn so that the column of the table that the cursor points to contains correctly formatted values, and not the integer that was returned Java.util.Date.getTime();when I added the item to the database?

+5
source share
3 answers
SELECT strftime("%m-%d-%Y", date_col, 'unixepoch') AS date_col

Your code will work if it expects a column of results in the format date_col.

EDIT: One thing you need to keep in mind is that getTime has been using milliseconds since 1970, while standard UNIX time (including SQLite) uses seconds.

+7
source

The method Java.util.Date.getTime();returns an integer that represents unix time ".

The easiest way to read this number as a date is to save it as is and read it using the following Sqlite query:

SELECT strftime('%m-%d-%Y', 1092941466, 'unixepoch');

which returns:

08-19-2004

, strftime .

, , 1000, . "" unix- , Java.util.Date.getTime(); .

+5

SQLite . - , .

, SQLite, :

  • NULL
  • INTEGER
  • BLOB

, but the date is stored as a long, unhelpful integer.

See Data Types in SQLite Version 3 for more information . For more information about saving date and time in SQLite, see SQL as understood by SQLite .

Hope this helps.

+2
source

More articles:


All Articles