Selecting from database where date is same month and day

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Selecting from database where date is same month and day

Post by Luke »

Alright I've got a mysql table of users with a date field for their birth dates. How would I go about selecting users who's birthday is today? I was looking at EXTRACT() but I can't figure out how to use it to do what I want. Would this be a job for regex?
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

What format is their birthday stored as?
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Post by veridicus »

How is the birthday stored? I hope you answer unix timestamp. Regex would definitely be a performance hit. If it's unix timestamp it's simple to query against the current day's timestamp.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

:oops: I already figured it out... I always post here right before I find the answer I'm looking for. :x

Code: Select all

function getBirthdays($date = null)
    {
        if (is_null($date))
        {
            $date = time();
        }
        
        $birthdate = getdate($date);
        
        $query = "
			SELECT
				username, DATE_FORMAT( birth_date, '%m-%d' ) AS bday
			FROM
				users
			WHERE
				MONTH( birth_date ) = '" . $birthdate['month'] . "'
			AND
				DAYOFMONTH( birth_date ) = '" . $birthdate['mday'] . "'
		";

        // perform query, etc.
    }
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Nah, I've got a better way

Code: Select all

$query = " 
                        SELECT 
                                username, DATE_FORMAT( birth_date, '%m-%d' ) AS bday 
                        FROM 
                                users 
                        WHERE 
                                MONTH( birth_date ) = MONTH(NOW()) 
                        AND 
                                DAY( birth_date ) = DAY(NOW()) 
                "; 

        // perform query, etc.
No need to use any PHP variables, just let MySQL do the work.[/syntax]
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

But what if Ninja needs to select a day other than today?
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Jenk wrote:But what if Ninja needs to select a day other than today?
Well Ninja's query was this
How would I go about selecting users who's birthday is today?
My answer does that
Post Reply