List: General Discussion « Previous MessageNext Message » From: Martin Ramsch Date: September 2 1999 5:19pm Subject: Re: DATES View as plain text
```On Thu, 1999-09-02 12:47:59 -0400, ATS wrote:
> 	I have three questions about dates.

Here we go with three answers ... ;-)

> !. If date of birth is stored in a DATE field format
> of 1952-12-31, what is the most efficient way to
> develop the persons's age?

This is more difficult than it should be ...

# Age in years.
SELECT FLOOR( PERIOD_DIFF(
DATE_FORMAT(CURRENT_DATE,'%Y%m')
,DATE_FORMAT('1969-05-06','%Y%m')
) / 12
) AS age_in_y;

Note: This doesn't take into account the day of birth,
so we have to subtract the day of month:

# Real age in years.
SELECT FLOOR(
PERIOD_DIFF(
DATE_FORMAT(
DATE_SUB(
CURRENT_DATE
,INTERVAL DAYOFMONTH('1969-05-06')-1 DAY
)
,'%Y%m'
)
,DATE_FORMAT('1969-05-06','%Y%m')
) / 12
) AS Alter_in_J;

# Age in months.
SELECT PERIOD_DIFF(
DATE_FORMAT(CURRENT_DATE,'%Y%m')
,DATE_FORMAT('1969-05-06','%Y%m')
) AS age_in_m;

# Age in days.
SELECT TO_DAYS(CURRENT_DATE)-TO_DAYS('1969-05-06') AS age_in_d;

Note: TO_DAYS can't handle dates before 1582!

# Age in hours ("HH:MM:SS").
SELECT SEC_TO_TIME(
UNIX_TIMESTAMP()-UNIX_TIMESTAMP('1969-05-06')
) AS age_in_h;

Note: Unix timestamps only work from 1970 until 2037.

# Age in seconds.
SELECT UNIX_TIMESTAMP()-UNIX_TIMESTAMP('1969-05-06') AS age_in_s;

Note: Unix timestamps only work from 1970 until 2037.

> 2. If a date is stared as above for a field,
> DateOfLastPurchase, what is the most efficient
> way to develop days since last purchase.

TO_DAYS(CURRENT_DATE)-TO_DAYS(DateOfLastPurchase)

> 3. Where is the best place to find one for
> Saturday night? (It's a joke!)

Bienenkorb in my town Passau (_very_ bad joke :)

Regards,
Martin
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
```