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
Thread
DATES(ATS)2 Sep
  • Re: DATESMartin Ramsch2 Sep
  • DATESMichael Widenius3 Sep
    • Re: DATES(ATS)3 Sep
      • Re: DATES(ATS)3 Sep
        • Re: DATESMartin Ramsch3 Sep