List:General Discussion« Previous MessageNext Message »
From:Alister West Date:April 22 2010 10:17pm
Subject:Re: First and Last timestamp of the day/week / month
View as plain text  
You could rewrite it english friendly

(5.1.37)

SET @DAY_START = curdate();
SET @WEEK_START = curdate()  - interval weekday(now()) DAY;
SET @MONTH_START = date_format(curdate(), "%Y-%m-01");

## DAY
SELECT timestamp(@DAY_START) as min_ts,
         timestamp(@DAY_START + INTERVAL 1 DAY
                              - INTERVAL 1 SECOND
         ) as max_ts ;

## WEEK
SELECT timestamp(@WEEK_START) as min_ts,
         timestamp(@WEEK_START + INTERVAL 1 WEEK
                               - INTERVAL 1 SECOND
        ) as max_ts ;

## MONTH
SELECT timestamp(@MONTH_START) as min_ts,
         timestamp(@MONTH_START + INTERVAL 1 MONTH
                                - INTERVAL 1 SECOND
        ) as max_ts  ;




~~
 c|_|  Alister West - Saving the world from coffee!




On 22 April 2010 14:25, Cantwell, Bryan <bcantwell@stripped> wrote:
> I need to be able to get a first and last timestamp for a day a week or a
> month. I have an example of what I did so far that gets me that info for a
> week... but I fear that it is far more complex than it needs to be. Anyone
> have a simple way to get first and last timestamp for these intervals?
>
> SELECT
>  timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1)))
>  , from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 *
> (DAYOFWEEK(CURDATE()) - 1)))+86399)
>
Thread
First and Last timestamp of the day/week / monthBryan Cantwell22 Apr
  • Re: First and Last timestamp of the day/week / monthAlister West23 Apr