7

I am using sqlite database in my application in which I am storing a Date and Time in milli-seconds. Now using Sqlite query I am trying to get this datetime from milliseconds to date in format "yyyy-MM-dd" but not getting a proper result.

I want to do this using a Sqlite query. Your help will be appreciated.

Dharmendra
  • 32,191
  • 22
  • 85
  • 127
  • 1
    Can you post what code you have, and what it is returning? Try: http://stackoverflow.com/questions/4327483/sqlite3-on-windows-convert-epoch-to-normal-time – RossC Aug 28 '12 at 11:41
  • 2
    http://stackoverflow.com/questions/8237193/java-convert-milliseconds-to-date – Dinesh Anuruddha Aug 28 '12 at 11:41
  • Didn't try with `SimpleDateFormat` class? – Praveenkumar Aug 28 '12 at 11:43
  • 1
    You can use [Strftime()](http://stackoverflow.com/questions/11630215/retrieve-date-wise-data-from-database-in-android/11638703#11638703) – Lalit Poptani Aug 28 '12 at 11:45
  • 1
    @RossC Thanks a lot. Your link has helped me. The datetime method takes the argument in seconds and I had try to set the milliseconds. Thanks again :) – Dharmendra Aug 28 '12 at 12:01
  • My pleasure, it's an easy thing to overlook. I still get counting from zero wrong at least once per program I write! Which I shouldn't admit to here! – RossC Aug 28 '12 at 12:16

2 Answers2

15

Datetime expects epochtime, which is in number of seconds while you are passing in milliseconds. Convert to seconds & apply. 

SELECT datetime(1346142933585/1000, 'unixepoch');

Can verify this from this fiddle

Sathyajith Bhat
  • 20,587
  • 21
  • 94
  • 129
3

Try:

select strftime("%Y-%m-%d", YOUR_DATE_COL) from .......
Vyacheslav Shylkin
  • 9,641
  • 5
  • 37
  • 34
  • Getting wrong result.. I am getting proper format but not getting true value for example I have 1346142933585 milli which is equals to "2012-08-28" but I am getting "1698-19-20". – Dharmendra Aug 28 '12 at 11:46