MySQL practical Tutorials part 8- SQL date and timestamp functions and miscellaneous exercises on that

 Date Math

SELECT * FROM people;

 

SELECT DATEDIFF(NOW(), birthdate) FROM people;

 

SELECT name, birthdate, DATEDIFF(NOW(), birthdate) FROM people;

 

SELECT birthdt FROM people;

 

SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;

 

SELECT birthdt, DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people;

 

SELECT birthdt, DATE_ADD(birthdt, INTERVAL 3 QUARTER) FROM people;

 

SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people;

 

SELECT birthdt, birthdt - INTERVAL 5 MONTH FROM people;

 

SELECT birthdt, birthdt + INTERVAL 15 MONTH + INTERVAL 10 HOUR FROM people;

=======================================================================================================================

Working with TIMESTAMPS

CREATE TABLE comments (

    content VARCHAR(100),

    created_at TIMESTAMP DEFAULT NOW()

);

 

INSERT INTO comments (content) VALUES('lol what a funny article');

 

INSERT INTO comments (content) VALUES('I found this offensive');

 

INSERT INTO comments (content) VALUES('Ifasfsadfsadfsad');

 

SELECT * FROM comments ORDER BY created_at DESC;

 

CREATE TABLE comments2 (

    content VARCHAR(100),

    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP

);

 

INSERT INTO comments2 (content) VALUES('dasdasdasd');

 

INSERT INTO comments2 (content) VALUES('lololololo');

 

INSERT INTO comments2 (content) VALUES('I LIKE CATS AND DOGS');

 

UPDATE comments2 SET content='THIS IS NOT GIBBERISH' WHERE content='dasdasdasd';

 

SELECT * FROM comments2;

 

SELECT * FROM comments2 ORDER BY changed_at;

 

CREATE TABLE comments2 (

    content VARCHAR(100),

    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW()

);

=======================================================================================================================

Data Types Exercises Solution

What's a good use case for CHAR?

------

Used for text that we know has a fixed length, e.g., State abbreviations, 

abbreviated company names, sex M/F, etc.

 

CREATE TABLE inventory (

    item_name VARCHAR(100),

    price DECIMAL(8,2),

    quantity INT

);

 

What's the difference between DATETIME and TIMESTAMP?

------

They both store datetime information, but there's a difference in the range, 

TIMESTAMP has a smaller range. TIMESTAMP also takes up less space. 

TIMESTAMP is used for things like meta-data about when something is created

or updated.

 

SELECT CURTIME();

 

SELECT CURDATE()';

 

SELECT DAYOFWEEK(CURDATE());

SELECT DAYOFWEEK(NOW());

SELECT DATE_FORMAT(NOW(), '%w') + 1;

 

SELECT DAYNAME(NOW());

SELECT DATE_FORMAT(NOW(), '%W');

 

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y');

 

SELECT DATE_FORMAT(NOW(), '%M %D at %h:%i');

 

CREATE TABLE tweets(

    content VARCHAR(140),

    username VARCHAR(20),

    created_at TIMESTAMP DEFAULT NOW()

);

 

INSERT INTO tweets (content, username) VALUES('this is my first tweet', 'coltscat');

SELECT * FROM tweets;

 

INSERT INTO tweets (content, username) VALUES('this is my second tweet', 'coltscat');

SELECT * FROM tweets;