First and Last timestamp of the day/week / month

--001636e1f81f890fce0484d9f213
Content-Type: text/plain; charset=ISO-8859-1

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)

--001636e1f81f890fce0484d9f213--

Re: First and Last timestamp of the day/week / month

You could rewrite it english friendly

(5.1.37)

SET @DAY_START =3D curdate();
SET @WEEK_START =3D curdate() - interval weekday(now()) DAY;
SET @MONTH_START =3D 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 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. Anyon=
e
> have a simple way to get first and last timestamp for these intervals?
>
> SELECT
> =A0timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1)))
> =A0, from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 *
> (DAYOFWEEK(CURDATE()) - 1)))+86399)
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg