mysql birthday reminder, leap year

Posted by moodh on Stack Overflow See other posts from Stack Overflow or by moodh
Published on 2010-02-08T19:49:30Z Indexed on 2010/05/04 0:58 UTC
Read the original article Hit count: 340

Filed under:

mysql

|

leap-year

Hi, I'm trying to sort out a result set that gives the 5 closest users sorted by upcoming birthday. This works perfectly until leap years comes into play. For example:

May 15th - 96 days left
May 15th - 97 days left

The top result is a birth at 1987 and the lower is from 1988. u_birth is stored as yyyy-mm-dd. Is there a simple way to sort this problem without having to rewrite the entire query?

SELECT u_birth, IF( DAYOFYEAR( u_birth ) >= DAYOFYEAR( NOW() ), 
          DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ), 
          DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ) +  
      DAYOFYEAR( CONCAT( YEAR( NOW() ), '-12-31' ) ) 
 )  
 AS distance
FROM (blog_users)
WHERE `s_agehide` = 0
ORDER BY distance ASC
LIMIT 5

This query is taken and modified from the mysql manual: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#c7489

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about leap-year

SQL SERVER – Various Leap Year Logics

as seen on SQL Authority - Search for 'SQL Authority'
Earlier I wrote one article on Leap Year and created one video about Leap Year. My point of view was to demonstrate how we can use SQL Server 2012 features to identify Leap year. How ever during the conversation I had some really good conversation. Here are updates for those who have missed reading… >>> More
Correct algorith for checking leap year

as seen on Stack Overflow - Search for 'Stack Overflow'
What will be the exact definition of leap year ? AFAIK "A year which is divisible by 4 is a leap year.But for century years' the years which are divisible by 400 is a leap year." But that definition makes 100, 200, 300, 400.... upto 1700 NOT LEAP years! But in Gregorian calender all of them are all… >>> More
Java Code for calculating Leap Year, is this code correct ?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello I am following "The Art and Science of Java" book and it shows how to calculate a leap year. The book uses ACM Java Task Force's library. Here is the code the books uses: import acm.program.*; public class LeapYear extends ConsoleProgram { public void run() { println("This… >>> More
Easy way to determine leap year in ruby?

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there an easy way to determine if a year is a leap year? >>> More
mysql birthday reminder, leap year

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I'm trying to sort out a result set that gives the 5 closest users sorted by upcoming birthday. This works perfectly until leap years comes into play. For example: May 15th - 96 days left May 15th - 97 days left The top result is a birth at 1987 and the lower is from 1988. u_birth is stored… >>> More