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
  • 33,296
  • 22
  • 86
  • 129
  • 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
  • 21,321
  • 22
  • 95
  • 134
  • 1
    +1 I am sure that this will work. – MKJParekh Aug 29 '12 at 05:08
  • This is what I am looking for. Thanks a lot. Just one modification is that if we want a time in local timezone then Use this query `SELECT datetime(1346142933585/1000, 'unixepoch', 'localtime');` – Dharmendra Aug 29 '12 at 05:20
  • 3
    @Dharmendra yeah, I thought of mentioning that(you'll note I tested it -> http://sqlfiddle.com/#!5/d41d8/224), but I didn't mention it :-) – Sathyajith Bhat Aug 29 '12 at 05:24
3

Try:

select strftime("%Y-%m-%d", YOUR_DATE_COL) from .......
Vyacheslav Shylkin
  • 9,741
  • 5
  • 39
  • 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