Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL check if datetime column is within weekend period

Tags:

datetime

mysql

I have a table with a datetime column in the database. Is there a way i can write my query to determine if the record is within the weekend period?

I want to display all records and have an additional column in each result row to display Weekend if it meets the criteria of (after Friday 1730hours) or (before sunday 2359hours).

The below is what I currently have but something seems to be wrong. :( any help will be greatly appreciated. :)

SELECT recordTime, DATE_FORMAT(recordTime, "%W %w %T"), (( (DATE_FORMAT(recordTime, "%T") > '17:30:00' AND DAYOFWEEK(recordTime)=6)
    AND (DATE_FORMAT(recordTime, "%T") < '23:59:59' AND DAYOFWEEK(recordTime)=7) )
    OR (DATE_FORMAT(recordTime, "%T") < '23:59:59' AND DAYOFWEEK(recordTime)=0)) AS Weekend
FROM `waitrecord`
like image 489
chongzixin Avatar asked Aug 27 '12 12:08

chongzixin


1 Answers

Your logic is wrong. A day cannot be weekday 6 and weekday 7 at the same time.

The condition should be

DAYOFWEEK(recordTime) = 7
or DAYOFWEEK(recordTime) = 1
or (DATE_FORMAT(recordTime, "%T") > '17:30:00' AND DAYOFWEEK(recordTime) = 6)

Edit: DAYOFWEEK actually returns 1 for Sunday and 7 for Saturday (in contrast to DATE_FORMAT which is zero based)

like image 165
Alex Monthy Avatar answered Oct 02 '22 16:10

Alex Monthy