CODEWITHSUNDEEP
javaandroidsqlitedatedatetime-format
Vrashabh Irde
Vrashabh Irde

Reputation: 14367

Java Current Date Time not recognized by Sqlite DateTime

I am using the following code in Android to get current date time 

Date currentDateTime = new Date();

And storing it in the sqlite table as 

currentDateTime.ToString()

SO in the table the values are stored like so

"Tue Sep 11 20:32:39 IST 2012"
"Thu Sep 13 17:23:38 IST 2012"
"Thu Sep 13 17:34:38 IST 2012"
"Thu Sep 13 20:21:58 IST 2012"

Now I need to sort them in sqlite in desc order i.e.

select datetime(datecolumn) from table order by datetime(datecolumn) desc;

But when I do datetime(column) on the field it shows me null for all the rows on the date field

Obviously there is a data mismatch on the sqlite side. But I am not sure how to fix it - Is there a way I can still change the query on the sqlite side to be able to read this? Or do I have to change the JAVA code and insert the data in a sqlite readable format.

I need the times also since I am dealing with hourly data.

Upvotes: 1

Views: 1633

Answers (2)

CL.
CL.

Reputation: 180020

See Date And Time Functions for the list of formats that are recognized as dates by SQLite.

If you want the database fields to be more readable than a single milliseconds value, format the strings as YYYY-MM-DD HH:MM:SS.

Upvotes: 1

dorjeduck
dorjeduck

Reputation: 7794

I would use

currentDateTime.getTime())

and store that value - less space needed in the database, and I assume much quicker to sort for sqlite

Upvotes: 4

Related Questions