Mega Code Archive

 

Categories / MySQL / Date Time

 

Sorting by Day of Week

mysql> mysql> CREATE TABLE event     -> (     ->  date            DATE,     ->  description     VARCHAR(255)     -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO event (date,description)     ->  VALUES     ->          ('1789-07-04','US Independence Day'),     ->          ('1776-07-14','Bastille Day'),     ->          ('1957-10-04','Sputnik launch date'),     ->          ('1958-01-31','Explorer 1 launch date'),     ->          ('1919-06-28','Signing of the Treaty of Versailles'),     ->          ('1732-02-22','George Washington\'s birthday'),     ->          ('1989-11-09','Opening of the Berlin Wall'),     ->          ('1944-06-06','D-Day at Normandy Beaches'),     ->          ('1215-06-15','Signing of the Magna Carta'),     ->          ('1809-02-12','database Lincoln\'s birthday'); Query OK, 10 rows affected (0.00 sec) Records: 10  Duplicates: 0  Warnings: 0 mysql> mysql> SELECT * FROM event; +------------+-------------------------------------+ | date       | description                         | +------------+-------------------------------------+ | 1789-07-04 | US Independence Day                 | | 1776-07-14 | Bastille Day                        | | 1957-10-04 | Sputnik launch date                 | | 1958-01-31 | Explorer 1 launch date              | | 1919-06-28 | Signing of the Treaty of Versailles | | 1732-02-22 | George Washington's birthday        | | 1989-11-09 | Opening of the Berlin Wall          | | 1944-06-06 | D-Day at Normandy Beaches           | | 1215-06-15 | Signing of the Magna Carta          | | 1809-02-12 | database Lincoln's birthday         | +------------+-------------------------------------+ 10 rows in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT DAYNAME(date) AS day, date, description     -> FROM event     -> ORDER BY DAYOFWEEK(date); +----------+------------+-------------------------------------+ | day      | date       | description                         | +----------+------------+-------------------------------------+ | Sunday   | 1809-02-12 | database Lincoln's birthday         | | Sunday   | 1776-07-14 | Bastille Day                        | | Monday   | 1215-06-15 | Signing of the Magna Carta          | | Tuesday  | 1944-06-06 | D-Day at Normandy Beaches           | | Thursday | 1989-11-09 | Opening of the Berlin Wall          | | Friday   | 1732-02-22 | George Washington's birthday        | | Friday   | 1958-01-31 | Explorer 1 launch date              | | Friday   | 1957-10-04 | Sputnik launch date                 | | Saturday | 1919-06-28 | Signing of the Treaty of Versailles | | Saturday | 1789-07-04 | US Independence Day                 | +----------+------------+-------------------------------------+ 10 rows in set (0.00 sec) mysql> mysql> drop table event; Query OK, 0 rows affected (0.00 sec)