Format integer to formatted date in an SQLite select statement

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

To keep my program simple, I would like to avoid using a Simp开发者_StackOverflowleArrayAdapter, and stick with the CursorAdapter.

Is it somehow possible to format the integer within the date colum as mm-dd-yyyy so that the column of the table, that the cursor is pointing to, contains properly formatted values rather than the integer that was returned by Java.util.Date.getTime(); when I added the item to the database?

SELECT strftime("%m-%d-%Y", date_col, 'unixepoch') AS date_col

Your code will work if it expects a result set column in that format called date_col.

EDIT: One thing you need to watch out for is that getTime uses milliseconds since 1970, while standard UNIX time (including SQLite) uses seconds.

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

The simplest way to read this number as a date is by storing it as-is, and reading it using the following Sqlite query:

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

which returns:

08-19-2004

If you need another format, you can use the strftime function to format is as you like, or any of the other date formats and functions available.

You'll have to, as Matthew Flaschen points out in a commend below, divide the date by 1000 before you are able to use them in this way. "Real" unix times are measured in seconds since the epoch, and Java.util.Date.getTime(); returns milliseconds since epoch.

SQLite uses static rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

Any value stored in the SQLite database has one of the following storage class:

1. NULL
2. INTEGER
3. REAL
4. TEXT
5. BLOB

so I am not sure what you meant by but the date is stored as a long, unhelpful integer.

For more details please refer to Datatypes In SQLite Version 3. For further information on storing date/time in SQLite please refer to SQL As Understood By SQLite.

I hope this helps.