android - Format integer to formatted date in an SQLite select statement

Question

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 SimpleArrayAdapter, 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?

Answer 1

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.