From: tony
Date: March 23 1999 12:50pm
Subject: How far into month?
List-Archive: http://lists.mysql.com/mysql/830
Message-Id: <19990323125038.7949.qmail@crux.blackstar.co.uk>


I want to do a query that sums up figures for the month to date, and
(assuming straight continuation) calculates an estimate for the entire
month.

Now, my current thinking is that this implies getting 
  UNIX_TIMESTAMP(), UNIX_TIMESTAMP(start_of_this_month),
  UNIX_TIMESTAMP(start_of_next_month)

and then calculating

   start_next_month - start_this_month
   -----------------------------------   x  current figure
                now - start_this_month

So far so good ...

However, to calculate the time_stamp of the start of this month / next
month is proving tricky. The UNIX_TIMESTAMP function doesn't seem to like
being passed a CONCAT(), so all my fancy trickery with MONTH(now()) + 1 etc  
is to no avail... 

I _could_ do all this outside the database queries in advance using
Perl, but that seems a little clumsy - unless someone knows of some nice
perl function already in existence that calculates % of way into month.

If there any way to do this in MySQL, or will I have to write / source
a perl function that does this calculation for me?

Or, is there a much simpler method that I'm missing!?

Thanks,

Tony
-- 
-----------------------------------------------------------------------------
 Tony Bowden | tony@stripped               http://www.blackstar.co.uk/
  Black Star |    The UK's Biggest Video & DVD store * Free Postage Worldwide
-----------------------------------------------------------------------------