Get data in SELECT MySQL by a day in field type timestamp

Question

I want select rows in where a day in specific i.e. "Monday", but my type column is a timestamp "AAAA-MM-DD HH:MM:SS". I've searched but I don't how to select this.

My table is this, and the field is forex_pair_price_time (timestamp):

mysql> describe forex_pair_price;
+-------------------------+----------------+------+-----+-------------------+----------------+
| Field                   | Type           | Null | Key | Default           | Extra          |
+-------------------------+----------------+------+-----+-------------------+----------------+
...
| forex_pair_price_time   | timestamp      | NO   |     | CURRENT_TIMESTAMP |                |
...
+-------------------------+----------------+------+-----+-------------------+----------------+

can you add a sample data and what you are trying to select? — Navid Yousefzai, Mar 02 '19 at 09:06
you can use SUBSTR function to get the year month and day and use code something like this to convert it to the days of the week, https://stackoverflow.com/questions/186431/calculating-days-of-week-given-a-week-number — Navid Yousefzai, Mar 02 '19 at 09:18

Johann · Answer 1 · 2019-03-02T11:09:35.847

http://www.mysqltutorial.org/mysql-weekday

Use WEEKDAY(date).

The WEEKDAY function returns a weekday index for a date i.e., 0 for Monday, 1 for Tuesday, … 6 for Sunday.

Select * from forex_pair_price 
where weekday(forex_pair_price_time)=0

Or

Dayname(forex_pair_price_time)='Monday'

I think it is saver to use weekday because it is an compare to an integer instead of string. This is saver against problems with typos in String.

score 0 · Answer 2 · answered Mar 02 '19 at 09:10

0

To find all records where the forex timestamp happen to land on Monday, we can try using DAYNAME:

SELECT *
FROM forex_pair_price
WHERE DAYNAME(forex_pair_price_time) = 'Monday';

answered Mar 02 '19 at 09:10

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 3 · answered Mar 02 '19 at 10:13

SELECT forex_pair_price_time,
CASE 
    WHEN DAYOFWEEK(orex_pair_price_time) = 1 THEN "Sunday"
    WHEN DAYOFWEEK(orex_pair_price_time) = 2 THEN "Monday"
    WHEN DAYOFWEEK(orex_pair_price_time) = 3 THEN "Tuesday"
    WHEN DAYOFWEEK(orex_pair_price_time) = 4 THEN "Wednesday"
    WHEN DAYOFWEEK(orex_pair_price_time) = 5 THEN "Thursday"
    WHEN DAYOFWEEK(orex_pair_price_time) = 6 THEN "Friday"
    WHEN DAYOFWEEK(orex_pair_price_time) = 7 THEN "Saturday"
  END Weekday
 FROM forex_pair_price

Get data in SELECT MySQL by a day in field type timestamp

3 Answers3