mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2015-11-30 18:13:23 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT YEAR(NOW()); +-------------+ | YEAR(NOW()) | +-------------+ | 2015 | +-------------+ 1 row in set (0.03 sec) mysql> SELECT CONCAT(DAY(NOW()),'.',MONTH(NOW())); +-------------------------------------+ | CONCAT(DAY(NOW()),'.',MONTH(NOW())) | +-------------------------------------+ | 30.11 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CONCAT('Kell on ', HOUR(NOW()), '.', MINUTE(NOW())); +-----------------------------------------------------+ | CONCAT('Kell on ', HOUR(NOW()), '.', MINUTE(NOW())) | +-----------------------------------------------------+ | Kell on 18.16 | +-----------------------------------------------------+ 1 row in set (0.00 sec) -- lisage ka sekundid mysql> SELECT DATE_ADD(NOW(), INTERVAL 32 DAY); +----------------------------------+ | DATE_ADD(NOW(), INTERVAL 32 DAY) | +----------------------------------+ | 2016-01-01 18:23:35 | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_ADD(NOW(), INTERVAL 32 SECOND); +-------------------------------------+ | DATE_ADD(NOW(), INTERVAL 32 SECOND) | +-------------------------------------+ | 2015-11-30 18:25:36 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDTIME(NOW(), '2:30'); +------------------------+ | ADDTIME(NOW(), '2:30') | +------------------------+ | 2015-11-30 20:55:49 | +------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_FORMAT(NOW(), '%d.%m.%Y %H:%i'); +--------------------------------------+ | DATE_FORMAT(NOW(), '%d.%m.%Y %H:%i') | +--------------------------------------+ | 30.11.2015 18:29 | +--------------------------------------+ 1 row in set (0.00 sec) CREATE TABLE ajad( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, algus DATETIME, ots DATETIME ); INSERT INTO ajad VALUES (DEFAULT, '2015-1-1', NOW()); mysql> SELECT * FROM ajad; +----+---------------------+---------------------+ | id | algus | ots | +----+---------------------+---------------------+ | 1 | 2015-01-01 00:00:00 | 2015-11-30 18:32:07 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) INSERT INTO ajad VALUES (DEFAULT, '2015-7-3', '2015-7-5'); INSERT INTO ajad VALUES (DEFAULT, '2015-7-3 12:30', '2015-7-3 14:40'); mysql> SELECT * FROM ajad; +----+---------------------+---------------------+ | id | algus | ots | +----+---------------------+---------------------+ | 1 | 2015-01-01 00:00:00 | 2015-11-30 18:32:07 | | 2 | 2015-07-03 00:00:00 | 2015-07-05 00:00:00 | | 3 | 2015-07-03 12:30:00 | 2015-07-03 14:40:00 | +----+---------------------+---------------------+ mysql> SELECT DATEDIFF(ots, algus) FROM ajad; +----------------------+ | DATEDIFF(ots, algus) | +----------------------+ | 333 | | 2 | | 0 | +----------------------+ 3 rows in set (0.00 sec) mysql> SELECT DATE_ADD(@omasynd, INTERVAL 2*DATEDIFF(@pojasynd, @omasynd) DAY); +------------------------------------------------------------------+ | DATE_ADD(@omasynd, INTERVAL 2*DATEDIFF(@pojasynd, @omasynd) DAY) | +------------------------------------------------------------------+ | 2040-04-17 | +------------------------------------------------------------------+ 1 row in set (0.00 sec) Lisage aegade tabelisse summa tulp Lisage tabelisse mõned tööd koos algus- ja lõpuaja ning summaga eurodes. Kuvage välja iga töö tunnitasu Järjestage tööd tunnitasu järgi ALTER TABLE ajad ADD summa DECIMAL(8, 2) DEFAULT 0; INSERT INTO ajad VALUES (DEFAULT, '2015-11-30 9:00', '2015-11-30 15:00', 30); INSERT INTO ajad VALUES (DEFAULT, '2015-11-30 9:00', '2015-11-30 18:00', 60); INSERT INTO ajad VALUES (DEFAULT, '2015-11-30 9:00', '2015-11-30 10:00', 10); mysql> SELECT TIMESTAMPDIFF(HOUR, algus, ots) as vahe_tundides FROM ajad; +---------------+ | vahe_tundides | +---------------+ | 8010 | | 48 | | 2 | | 26328 | | 6 | | 9 | | 1 | +---------------+ mysql> SELECT *, summa/TIMESTAMPDIFF(HOUR, algus, ots) as tunnitasu FROM ajad; +----+---------------------+---------------------+-------+-----------+ | id | algus | ots | summa | tunnitasu | +----+---------------------+---------------------+-------+-----------+ | 1 | 2015-01-01 00:00:00 | 2015-11-30 18:32:07 | 0.00 | 0.000000 | | 2 | 2015-07-03 00:00:00 | 2015-07-05 00:00:00 | 0.00 | 0.000000 | | 3 | 2015-07-03 12:30:00 | 2015-07-03 14:40:00 | 0.00 | 0.000000 | | 4 | 2012-07-03 00:00:00 | 2015-07-05 00:00:00 | 0.00 | 0.000000 | | 5 | 2015-11-30 09:00:00 | 2015-11-30 15:00:00 | 30.00 | 5.000000 | | 6 | 2015-11-30 09:00:00 | 2015-11-30 18:00:00 | 60.00 | 6.666667 | | 7 | 2015-11-30 09:00:00 | 2015-11-30 10:00:00 | 10.00 | 10.000000 | +----+---------------------+---------------------+-------+-----------+ mysql> SELECT *, summa/TIMESTAMPDIFF(HOUR, algus, ots) as tunnitasu FROM ajad ORDER BY tunnitasu DESC; +----+---------------------+---------------------+-------+-----------+ | id | algus | ots | summa | tunnitasu | +----+---------------------+---------------------+-------+-----------+ | 7 | 2015-11-30 09:00:00 | 2015-11-30 10:00:00 | 10.00 | 10.000000 | | 6 | 2015-11-30 09:00:00 | 2015-11-30 18:00:00 | 60.00 | 6.666667 | | 5 | 2015-11-30 09:00:00 | 2015-11-30 15:00:00 | 30.00 | 5.000000 | | 1 | 2015-01-01 00:00:00 | 2015-11-30 18:32:07 | 0.00 | 0.000000 | | 2 | 2015-07-03 00:00:00 | 2015-07-05 00:00:00 | 0.00 | 0.000000 | | 3 | 2015-07-03 12:30:00 | 2015-07-03 14:40:00 | 0.00 | 0.000000 | | 4 | 2012-07-03 00:00:00 | 2015-07-05 00:00:00 | 0.00 | 0.000000 | +----+---------------------+---------------------+-------+-----------+ 7 rows in set (0.00 sec) mysql> SELECT aasta, if(aasta mod 2 =0, 'paaris', 'paaritu') FROM soiduautod; +-------+-----------------------------------------+ | aasta | if(aasta mod 2 =0, 'paaris', 'paaritu') | +-------+-----------------------------------------+ | 1988 | paaris | | 1989 | paaritu | | 1988 | paaris | | 1998 | paaris | +-------+-----------------------------------------+