List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 23 1999 1:40pm
Subject:Re: How far into month?
View as plain text  
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

Thread
How far into month?tony23 Mar
  • Re: How far into month?Christian Mack23 Mar