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)

results matching ""

    No results matching ""