Select MySQL Timestamp at start or end of day or week

Posted on: October 16th, 2013 by admin 1 Comment

Code to select MySQL timestamp at the start, or beginning of the day.

SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00"))

And for a timestamp at the end of the day:

SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW(),"%Y-%m-%d 23:59:59"))

Timestamp for the beginning of the week (Monday morning):

SELECT UNIX_TIMESTAMP(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) day), "%Y-%m-%d 00:00:00"))

And timestamp for the end of the week (Sunday evening):

SELECT UNIX_TIMESTAMP(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 6  day), "%Y-%m-%d 23:59:59"))