From: Christian Mack
Date: March 23 1999 1:40pm
Subject: Re: How far into month?
List-Archive: http://lists.mysql.com/mysql/833
Message-Id: <36F799CF.DE2ABB2B@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

tony@stripped wrote:
> 
> 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

Hi Tony

Try something like:
UNIX_TIMESTAMP( CONCAT( YEAR( NOW())
			, LPAD( MONTH( NOW() ), 2, '0')
			, '01000000') 
		+ 0 )

for start_this_month.

UNIX_TIMESTAMP( CONCAT( YEAR( NOW())
			, LPAD( MONTH( NOW() ) + 1, 2, '0')
			, '01000000') 
		+ 0 )

for start_next_month.

Tschau
Christian