SQL Database Administration Day 5

DATE FUNCTIONS
==============
ADDDATE/ DATE_ADD is used to add a number of days, months or year to a date

for instance there are a set of projects to be done for a number of days, you can use this to determine the end of the projects

datestarted, project, duration,dateofsubmission (will be calculated with dateadd), who is doing the project

example using stud_registration

ADDING DAYS
-----------
SELECT SURNAME, FIRSTNAME, OTHERNAMES, DATEOFBIRTH,
ADDDATE(DATEOFBIRTH, INTERVAL 10 DAY) AS DOB_PLUS_10_DAYS
FROM STUD_REGISTRATION

ADDING MONTHS
--------------
SELECT SURNAME, FIRSTNAME, OTHERNAMES, DATEOFBIRTH,
ADDDATE(DATEOFBIRTH, INTERVAL 10 MONTH) AS DOB_PLUS_10_DAYS
FROM STUD_REGISTRATION

ADDING YEARS
-----------

PROJECT BY 2050, WIN 100 MILLION SOULS - 32 YEARS FROM TODAY (6-FEBRUARY 2018)

SELECT SURNAME, FIRSTNAME, OTHERNAMES,
CURDATE() AS STARTDATE, ADDDATE(CURDATE(), INTERVAL 32 YEAR) AS ENDYEAR
FROM STUD_REGISTRATION

SELECT SURNAME, FIRSTNAME, OTHERNAMES,
CURDATE(), ADDDATE(CURDATE(), INTERVAL -32 YEAR) AS ENDYEAR
FROM STUD_REGISTRATION

use this interval

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH



ADDTIME
-------
is used to add a number of seconds to a time with the date values: adding 120 seconds

SELECT SURNAME, FIRSTNAME, OTHERNAMES,
DATEOFBIRTH,
ADDTIME(DATEOFBIRTH, "120") AS DOB
FROM STUD_REGISTRATION

adding a number of hours,minutes and seconds
-------------------------------------------

SELECT SURNAME, FIRSTNAME, OTHERNAMES, DATEOFBIRTH, ADDTIME(DATEOFBIRTH, "1:39:50") AS DOB FROM STUD_REGISTRATION


server current time
-------------------

CURDATE(), CURRENT_DATE()
CURRENT_TIME()

SELECT CURDATE()
SELECT CURRENT_DATE()
SELECT CURRENT_TIME()

SELECT CURRENT_TIMESTAMP();
SELECT NOW();
SELECT SYSDATE()

2018-02-06 07:13:25

local current time
------------------
SELECT LOCALTIME()
SELECT LOCALTIMESTAMP() - local current date and time

MONTH

is used to get data based on month with a date

for instance get all students born in august of any year


select * from
STUD_REGISTRATION
WHERE MONTH(DATEOFBIRTH) =8


YEAR

ALL those born in 1970

select *
from STUD_REGISTRATION
WHERE YEAR(DATEOFBIRTH) =1970

DAY

select *
from STUD_REGISTRATION
WHERE DAY(DATEOFBIRTH) =28


WEEKDAY

all those born on particular day of the week
(monday to sunday)

0 = Monday,
1 = Tuesday,
2 = Wednesday,
3 = Thursday,
4 = Friday,
5 = Saturday,
6 = Sunday.

all born on tuesday
------------------
select SURNAME, FIRSTNAME, DATEOFBIRTH
from STUD_REGISTRATION
WHERE WEEKDAY(DATEOFBIRTH) =1

OR

select SURNAME, FIRSTNAME, DATEOFBIRTH
from STUD_REGISTRATION
WHERE DAYNAME(DATEOFBIRTH) = "Tuesday"


QUARTER

All born in a particular of the quarter of the year

Quarter 1 = January-March
Quarter 2 = April-June
Quarter 3 = July-Sep
Quarter 4 = Oct-Dec

All those born on 3rd quarter of the year

select SURNAME, FIRSTNAME, DATEOFBIRTH
from STUD_REGISTRATION
WHERE QUARTER(DATEOFBIRTH) =3


MONTHNAME

select *
from STUD_REGISTRATION
WHERE MONTHNAME(DATEOFBIRTH) = "August"

WEEK
----

retunrs the week of the year with an option of which day is the first day of the week

SELECT WEEK("2017-06-15", 0);

0 - First day of week is Sunday
1 - First day of week is Monday and the first week has more than 3 days
2 - First day of week is Sunday
3 - First day of week is Monday and the first week has more than 3 days
4 - First day of week is Sunday and the first week has more than 3 days
5 - First day of week is Monday
6 - First day of week is Sunday and the first week has more than 3 days
7 - First day of week is Monday