dates
Date: April 22nd 2016
Last updated: April 22nd 2016
Im using MySQL for this section. Visit this website; http://www.w3schools.com/sql/sql_dates.asp, for more information on other SQL databases.
My example will look at two tables; surfdiary and board. The table surfdiary has a foreign key to the board table. There are two date entries in the surfdiary including start and end times.
surfdiary table column names
SELECT column_name FROM
information_schema.columns WHERE
table_name='surferprofile_surfdiary';
/*+-------------+
| column_name |
+-------------+
| id |
| wavesize |
| starttime |
| endtime |
| wavequality |
| selfscore |
| crowdscore |
| winddir |
| tide |
| notes |
| board_id |
| fin_id |
| trick_id |
| user_id |
| wave_id |
| wavetype |
+-------------+
16 rows in set (0.00 sec)
board table column names
SELECT column_name FROM
information_schema.columns WHERE
table_name='surferprofile_board';
/*+-------------------+
| column_name |
+-------------------+
| id |
| boardname |
| boardtype |
| boardperformance |
| boardtail |
| boardconstruction |
| boardglassing |
| boardlength |
| boardwidth |
| boardthickness |
| boardvolume |
| boardrocker |
| boardrail |
| boardconcave |
| boardmake |
| boardshaper |
| user_id |
+-------------------+
17 rows in set (0.00 sec)
Date columns
SELECT starttime, endtime FROM
surferprofile_surfdiary;
/*+---------------------+---------------------+
| starttime | endtime |
+---------------------+---------------------+
| 2016-04-21 00:30:00 | 2016-04-21 01:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
DATE function
SELECT DATE(starttime) AS Date FROM
surferprofile_surfdiary;
/*+------------+
| Date |
+------------+
| 2016-04-21 |
+------------+
1 row in set (0.00 sec)
TIME function
SELECT TIME(starttime) AS TIME FROM
surferprofile_surfdiary;
/*+----------+
| TIME |
+----------+
| 00:30:00 |
+----------+
1 row in set (0.00 sec)
Calculate the difference in date
SELECT DATE(DATE(starttime)-DATE(endtime)) AS diff FROM
surferprofile_surfdiary;
/*+------------+
| diff |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
DATEDIFF function
same as above
SELECT DATEDIFF(starttime, endtime) FROM
surferprofile_surfdiary;
+------------------------------+
| datediff(starttime, endtime) |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)
Calculate the difference in date using NOW function
how long ago did an event take place... 1 day ago
SELECT DATEDIFF(NOW(), endtime) FROM
surferprofile_surfdiary;
/*+--------------------------+
| DATEDIFF(NOW(), endtime) |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
Calculate the number of hours an event took place using CURTIME() function
SELECT CURTIME(), endtime, ROUND((CURTIME()-TIME(endtime))/3600,2) FROM
surferprofile_surfdiary;
+-----------+---------------------+-----------------------------------------+
| CURTIME() | endtime | ROUND((CURTIME()-TIME(endtime))/3600,2) |
+-----------+---------------------+-----------------------------------------+
| 10:50:08 | 2016-04-21 01:30:00 | 25.56 |
+-----------+---------------------+-----------------------------------------+
1 row in set (0.00 sec)
Calculate the difference in time
SELECT TIME(TIME(starttime)-TIME(endtime)) AS diff FROM
surferprofile_surfdiary;
+-----------+
| diff |
+-----------+
| -01:00:00 |
+-----------+
1 row in set (0.00 sec)
calculate the difference in time for each board
note this requires a table join from surfdiary to the board table
SELECT boardmake, TIME(TIME(endtime)-TIME(starttime)) as time_used FROM
surferprofile_surfdiary JOIN
surferprofile_board ON
surferprofile_surfdiary.board_id = surferprofile_board.id;
/*+-----------+-----------+
| boardmake | time_used |
+-----------+-----------+
| Hectic | 01:00:00 |
+-----------+-----------+
1 row in set (0.00 sec)
Group the boards by date
note that GROUP BY and ORDER BY are using shortcuts where 1 represents the first column (date) and 2 represents the boardmake
SELECT DATE(starttime) AS date, boardmake, TIME(TIME(endtime)-TIME(starttime)) AS time_used FROM
surferprofile_surfdiary JOIN
surferprofile_board ON
surferprofile_surfdiary.board_id = surferprofile_board.id
GROUP BY 1
ORDER BY 1, 2;
+------------+-----------+-----------+
| date | boardmake | time_used |
+------------+-----------+-----------+
| 2016-04-21 | Hectic | 01:00:00 |
+------------+-----------+-----------+
1 row in set (0.00 sec)