79

Every record in my SQLite database contains a field which contains a Date stored as a string in the format 'yyyy-MM-dd HH:mm:ss'.

Is it possible to query the database to get the record which contains the most recent date please?

Community
  • 1
  • 1
duncanportelli
  • 3,161
  • 8
  • 38
  • 59
  • 8
    You should store the date in milliseconds and convert every time you need to display it. This will then make it easier when sorting etc. – Andrei Dec 30 '12 at 13:45

10 Answers10

161

you can do it like this

SELECT * FROM Table ORDER BY date(dateColumn) DESC Limit 1
MilapTank
  • 9,988
  • 7
  • 38
  • 53
Paritosh
  • 2,303
  • 2
  • 18
  • 24
  • 64
    note that if you want to order by date **and time** you need to use `datetime()` intead of `date()` – eliocs Aug 29 '14 at 12:00
  • 4
    what if date string is in format "dd-mm-yyyy"? for this ur answer is not working – TejpalBh Jan 24 '19 at 05:24
  • 1
    This answered confused me. for me no need to use the "date" or "datetime" function around the date field name. check Nabi K.A.Z. answer – user1105951 Feb 20 '20 at 12:22
48

For me I had my query this way to solve my problem

select *  from Table order  by datetime(datetimeColumn) DESC LIMIT 1

Since I was storing it as datetime not date column

Ahmad Baraka
  • 1,093
  • 9
  • 12
12

When you sure the format of text field is yyyy-MM-dd HH:mm:ss (ex.: 2017-01-02 16:02:55), So It works for me simply:

SELECT * FROM Table ORDER BY dateColumn DESC Limit 1

Without any extra date function!

Nabi K.A.Z.
  • 9,887
  • 6
  • 59
  • 81
6

You need to convert it to unix timestamp, and then compare them:

SELECT * FROM data ORDER BY strftime('%s', date_column) DESC

But this can be pretty slow, if there are lots of rows. Better approach would be to store unix timestamp by default, and create an index for that column.

Zaffy
  • 16,801
  • 8
  • 50
  • 77
4

You can convert your column sent_date_time to yyyy-MM-dd format and then order by date:

1) substr(sent_date_time,7,4)||"-"||substr(sent_date_time,1,2)||"-"||substr(sent_date_time,4,2) as date
2) order by date desc
Christos Lytras
  • 36,310
  • 4
  • 80
  • 113
bpatel
  • 41
  • 1
2

In my case everything works fine without casting column to type 'date'. Just by specifying column name with double quotes like that:

SELECT * FROM 'Repair' ORDER BY "Date" DESC;

I think SQLite makes casting by itself or something like that, but when I tried to 'cast' Date column by myself it's not worked. And there was no error messages.

Dmitry
  • 321
  • 1
  • 3
  • 10
  • This is not working if I think Sqlite uses the date/time the cell data were added , when I change one cell `Dat` column it's still sort it after newer date like this https://i.ibb.co/Qv1zKQC/sqlitedatetime.png – Salem Jun 17 '19 at 07:06
1

You can also use the following query

"SELECT * FROM Table ORDER BY strftime('%Y-%m-%d %H:%M:%S'," + dateColumn + ") DESC  Limit 1"
ZIRES
  • 276
  • 3
  • 10
1

I found this ugly hack worked.

select *, substr(date_col_name,7,4)as yy, substr(date_col_name,4,2) as mm, substr(date_col_name,1,2) as dd from my_table order by yy desc,mm desc,dd desc

it would be better to convert the text column to date field type, but I found that did not work reliably for me.

CodingMatters
  • 1,275
  • 16
  • 26
0

If you do a lot of date sorting/comparison, you may get better results by storing time as ticks rather than strings, here is showing how to get 'now' in ticks with:

((strftime('%s', 'now') - strftime('%S', 'now') + strftime('%f', 'now')) * 1000)

(see https://stackoverflow.com/a/20478329/460084)

Then it's easy to sort, compare, etc ...

kofifus
  • 17,260
  • 17
  • 99
  • 173
  • 1) The OP has a SARGable value, so the conversion wouldn't be necessary. 2) You're converting `NOW` instead of the stored value, so that's not terribly helpful... 3) There appear to be better conversions (like the accepted answer), negating the need for the manual conversion. What benefit does your answer provide over any of the others? – Clockwork-Muse Nov 27 '18 at 23:13
  • 1) SARG ? not sure what that is .. 2) 'now' was just an example, updated my answer, 3) accepted answer will convert string to int before every comparison which is very inefficient and I think will not work for sorted indexes – kofifus Nov 27 '18 at 23:30
0

This will work for both date and time

SELECT * 
FROM Table 
ORDER BY 
julianday(dateColumn) 
DESC Limit 1
Gerhard Stein
  • 1,543
  • 13
  • 25
Venkatesh masanolla
  • 1