Your second query can be converted to MySQL in two ways.
First one is simple query, that adds interval
of days to compared date, depending on which dayofweek
it is. It's not the most elegant solution, and different days of week require different values (or selects).
Code below uses CURDATE
as a starting value, query for each column can be used separately:
SELECT
DATE(CURDATE()) AS now_date,
DATE_ADD(DATE(CURDATE()), INTERVAL (9 - IF(DAYOFWEEK(CURDATE()) < 2, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_monday,
DATE_ADD(DATE(CURDATE()), INTERVAL (10 - IF(DAYOFWEEK(CURDATE()) < 3, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_tuesday,
DATE_ADD(DATE(CURDATE()), INTERVAL (11 - IF(DAYOFWEEK(CURDATE()) < 4, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_wednesday,
DATE_ADD(DATE(CURDATE()), INTERVAL (12 - IF(DAYOFWEEK(CURDATE()) < 5, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_thursday,
DATE_ADD(DATE(CURDATE()), INTERVAL (13 - IF(DAYOFWEEK(CURDATE()) < 6, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_friday,
DATE_ADD(DATE(CURDATE()), INTERVAL (14 - IF(DAYOFWEEK(CURDATE()) < 7, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_saturday,
DATE_ADD(DATE(CURDATE()), INTERVAL (8 - DAYOFWEEK(CURDATE())) DAY) AS next_sunday;
Code found and copied from old GizmoLA post's comment. Similar one, just for Sunday, was explained on StackOverflow.
Second one is more universal, but requires creating function. In this case it's code could look like this one, found on SQLines post:
CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
RETURNS DATETIME
BEGIN
DECLARE start DATETIME;
DECLARE i INT;
-- Select the next date
SET start = ADDDATE(start_date, 1);
SET i = 1;
days: LOOP
-- Compare the day names
IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN
LEAVE days;
END IF;
-- Select the next date
SET start = ADDDATE(start, 1);
SET i = i + 1;
-- Not valid weekday specified
IF i > 7 THEN
SET start = NULL;
LEAVE days;
END IF;
END LOOP days;
RETURN start;
END;
Usage (show next sunday):
SELECT NEXT_DAY(NOW(), 'Sun');
You can also use NEXT_DAY and SUBDATE functions to get the previous date:
SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7);
I found a solution for this.
As you can see the time format is;
2012-08-07T09:00:00
I could easily strip the "t" but that wouldn't work and could(?) give me some trouble. I found out that the lib I was using (feedparser) could also return me a Python time object, looking like this:
time.struct_time(tm_year=2012, tm_mon=8, tm_mday=7, tm_hour=19, tm_min=0, tm_sec=22, tm_wday=1, tm_yday=220, tm_isdst=0)
Basicly.. a time.struct_time type.
With an example, you can see what I have done (examples made with iPython)
In [83]: date
Out[83]: time.struct_time(tm_year=2012, tm_mon=8, tm_mday=7, tm_hour=15,tm_min=51, tm_sec=19, tm_wday=1, tm_yday=220, tm_isdst=0)
In [84]: date = datetime.datetime(*date[:-3]).strftime("%Y-%m-%d %H:%M:%S")
In [85]: date
Out[85]: '2012-08-07 15:51:19'
Voila!
Best Answer
I think the answer is... Use MySQL to store dates/datetimes/timestamps in the limited formats that it provides, then use app code -- possibly some library in your app language -- to get at fancier formatting.
As a general rule, a database is responsible for storing; the application is responsible for formatting.
See also https://dev.mysql.com/doc/refman/5.7/en/locale-support.html