-1

I have a date stored as string in a Sqlite column, the format is YYYY-MM-DD ( Not separated into three columns for year date and month) Now I have a requirement to make a query which gives me results with the closest date to the date the query is being made as the first entry and the order goes on in ascending order.Here I am not interested in the year, only the month and the day matters. As per my requirement, what type of query should I process so that I get these dates in order.

Example:

I have the dates

1980-01-11, 1999-08-03, 2013-12-03 After the Query I should get:(Based on current date, considering it is December 1 2013)

2013-12-03,1999-01-11,1980-08-03 (Sort based only on month and day).

I also took reference from here :MySQL query to sort upcoming birthdays based on current date But it is not much clear. I think Unix timestamps as long values stored in date column would aid the type of query I am interested in, rather than the simple date format.

Community
  • 1
  • 1
Skynet
  • 7,820
  • 5
  • 44
  • 80
  • Only month and day? So if the input date for the query was `XXXX-07-01` then the last date in the result should be `XXXX-01-01` (if it exists, of course)? – Mosty Mostacho Dec 02 '13 at 04:59
  • It is totally dependent on the current date, say if it was XXXX-31-12, the first date in the query result should be XXXX-01-01. – Skynet Dec 02 '13 at 05:02
  • Please explain the downvote? I would love to improve. – Skynet Dec 02 '13 at 05:07
  • I think the downvote was related to not giving any trials from your side. Anyway, date handling is a bit weird as it is like cycle because the farthest you can be from the given date (I know, today, but could be any date) is half a year – Mosty Mostacho Dec 02 '13 at 05:14
  • That was the reason why SO was created, I wanted a heading and did not ask anyone to do my homework. – Skynet Dec 02 '13 at 05:27
  • Do you want to do it in MySQL or php? – TimSPQR Dec 02 '13 at 05:30
  • I want to do it in Sqlite. – Skynet Dec 02 '13 at 05:35
  • Ooops, sorry. My bad. Stupid question. – TimSPQR Dec 02 '13 at 05:39
  • When you say "closest", do you mean "closest following", or "closest following or preceding"? That is, should `2000-11-25` be sorted at the end? (And if you want to create a birthday list, why don't you just say so?) – CL. Dec 02 '13 at 09:05
  • Yes exactly CL, the birthday list! I mean closest only in terms of "Coming". – Skynet Dec 02 '13 at 10:59

2 Answers2

6

Following will do:

SELECT *
FROM _table
ORDER BY SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6), SUBSTR(birthdate, 6)

Check it in SQL Fiddle.

SUBSTR(date, 6) will remove year part from date. Birthdates must be sorted in MM-DD, but first should be shown those which will happen after today, and then those which happen before today.

SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6) will return 0 for birthdates MM-DD greater or equal to today MM-DD, 1 otherwise. So, first expression will ORDER past dates after comming date. Second parameter just ORDER dates by MM-DD.

See step by step in SQL Fiddle.

LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
2

There are quite a few ways to do it, some more efficient than others. I will suggest a solution that can be effective without changing your database format, but note that it can all get much faster (especially if we are talking about a big birthday list) if you can use separate Year,Month,Day integer columns, or a unix timestamp column.

select *, 
strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', birthday) || '-' || 
    strftime('%d', birthday)
)
- strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', 'now') || '-' || 
    strftime('%d', 'now')
)
as daydiff
from users
where daydiff >=0

union all

select *, 
strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', birthday) || '-' || 
    strftime('%d', birthday)
)
- strftime('%j', 
    strftime('%Y', 'now','+1 year') || '-' || 
    strftime('%m', 'now') || '-' || 
    strftime('%d', 'now')
) + 366
as daydiff
from users
where daydiff <366
order by daydiff

The above uses today's year and each users month and day parts to calculate day of year (e.g. today Dec 11, 2013 is day 345) and subtracts the day of year of today's date. Birthdays that occur in the current year will have a daydiff value >= 0, so we use them first. This was the first part of the union.

The second part makes the same calculation but for those whose birthday is next year, so we add 366 to the daydiff value and make sure we only get the fields that we didn't get from the first part.

The same query can be rewritten with a CASE WHEN instead of union. The CASE alternative will be faster because it will only fetch the rows from the users table just once, instead of twice, but it would be really ugly for me to write in this forum. On second thought, I'll write it anyway, since it is faster

select *, 
CASE WHEN 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) >= 0
THEN 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) 
ELSE 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) + 366 
END
as daydiff
from users
order by daydiff

Also a last note. I manually add 366 on next years birthdays, but the correct thing would be to add the days of year depending on the year (365 or 366). Since we only need it for ordering, this will not cause trouble because the worst case is that it will add one to daydiff for all next year's users. So birthday on '2013-12-31' will give daydiff=20, but birthday on '2014-01-01' will give daydiff=22.

EDIT:

Here's a fiddle

foibs
  • 3,258
  • 1
  • 19
  • 13