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

```