Mysql get the next Monday at noon

Question

Mysql get the next Monday at noon

Navigation

#1 by (9 votes)

3

Hello friends, could you help me with the following: I have a field with a timestamp format and I would like to know how to get the next Monday at midday to save it in date_end .

journay     date_start  date_end
1           1515200400  1515369600
2           1515805200  1515974400
3           1516410000  1516579200
4           1517014800  1517184000
5           1517619600  1517788800

I have the following query in which if I get Monday but at 6pm and I would like it at noon, that is to say at 12:00 pm

select journay, DATE_ADD(FROM_UNIXTIME(max(date_game_large)), INTERVAL (9 - IF(DAYOFWEEK(FROM_UNIXTIME(max(date_game_large)))=1, 8, DAYOFWEEK(FROM_UNIXTIME(max(date_game_large))))) DAY) as NEXTMONDAY from tablaone GROUP by journay order by journay


id_competition  journay .   date_start  date_end
385 1   1515200400  2018-01-08 18:00:00
385 2   1515805200  2018-01-15 18:00:00
385 3   1516410000  2018-01-22 18:00:00
385 4   1517014800  2018-01-29 18:00:00
385 5   1517619600  2018-02-05 18:00:00

Could you help me please

mysql datetime timestamp

asked by skycomputer2 24.01.2018 в 02:57

source

1 answer

How to determine the sublevels of an array? Using a recursive function Does anyone explain to me how this code works, to turn on and off a led?

score 9 · Accepted Answer

Try this:

SELECT 
    DATE_FORMAT(
        ADDDATE(
            FROM_UNIXTIME(date_game_large),
            7-WEEKDAY(FROM_UNIXTIME(date_game_large))
        ),
        '%Y-%m-%d 12:00:00'
    ) AS NEXTMONDAY
FROM tablaone

Functions we use:

FROM_UNIXTIME : Convert your timestamp to datetime.
WEEKDAY : Returns in number the day of the week (Monday 0, Tuesday 1 ...)
ADDDATE : Add the number of days you indicate to the given date.

At this point we add to the date one week less the WEEKDAY of the date. In this way, if we have a date that is Monday we add 7 days (7-0), if it is Tuesday 6 days (7-1), if it is Wednesday 5 days (7-2) ...
DATE_FORMAT : Give the proposed format to your field.
This is where we tell you that you want to always set time as 12:00:00