StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <pre><code>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 </code></pre> <p>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 <code>union</code>. </p> <p>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.</p> <p>The same query can be rewritten with a <code>CASE WHEN</code> instead of <code>union</code>. 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</p> <pre><code>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 </code></pre> <p>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. </p> <p>EDIT:</p> <p>Here's a <a href="http://sqlfiddle.com/#!5/24dab/1" rel="nofollow">fiddle</a> </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload