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
