SQL/MySQL/Date Time/Date Time Function — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:46, 26 мая 2010
Using Date/Time Functions in Your SQL Statements
<source lang="sql">
/* mysql> Drop table Transactions; Query OK, 0 rows affected (0.10 sec) mysql> CREATE TABLE Transactions (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> OrderID INT NOT NULL, -> DVDID SMALLINT NOT NULL, -> DateOut DATE NOT NULL, -> DateDue DATE NOT NULL, -> DateIn DATE NOT NULL -> ) -> ENGINE=INNODB;
Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
-> VALUES (1, 1, CURDATE(), CURDATE()+3), -> (1, 4, CURDATE(), CURDATE()+1), -> (1, 8, CURDATE(), CURDATE()+2), -> (2, 3, CURDATE(), CURDATE()+3), -> (3, 4, CURDATE(), CURDATE()+4), -> (3, 1, CURDATE(), CURDATE()+5), -> (3, 7, CURDATE(), CURDATE()+6), -> (4, 4, CURDATE(), CURDATE()+7), -> (5, 3, CURDATE(), CURDATE()+8), -> (6, 2, CURDATE(), CURDATE()+7), -> (6, 1, CURDATE(), CURDATE()+6), -> (7, 4, CURDATE(), CURDATE()+6), -> (8, 2, CURDATE(), CURDATE()+5), -> (8, 1, CURDATE(), CURDATE()+4), -> (8, 3, CURDATE(), CURDATE()+4), -> (9, 7, CURDATE(), CURDATE()+3), -> (9, 1, CURDATE(), CURDATE()+3), -> (10, 5, CURDATE(), CURDATE()+1), -> (11, 6, CURDATE(), CURDATE()+2), -> (11, 2, CURDATE(), CURDATE()+6), -> (11, 8, CURDATE(), CURDATE()+5), -> (12, 5, CURDATE(), CURDATE()+4), -> (13, 7, CURDATE(), CURDATE()+3);SELECT ID, YEAR(DateOut) AS YearOu
t Query OK, 23 rows affected (0.08 sec) Records: 23 Duplicates: 0 Warnings: 0
-> FROM Transactions -> WHERE ID>15 -> ORDER BY ID;
+----+---------+ | ID | YearOut | +----+---------+ | 16 | 2005 | | 17 | 2005 | | 18 | 2005 | | 19 | 2005 | | 20 | 2005 | | 21 | 2005 | | 22 | 2005 | | 23 | 2005 | +----+---------+ 8 rows in set (0.00 sec)
- /
Drop table Transactions; CREATE TABLE Transactions (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, OrderID INT NOT NULL, DVDID SMALLINT NOT NULL, DateOut DATE NOT NULL, DateDue DATE NOT NULL, DateIn DATE NOT NULL )
ENGINE=INNODB; INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue) VALUES (1, 1, CURDATE(), CURDATE()+3),
(1, 4, CURDATE(), CURDATE()+1), (1, 8, CURDATE(), CURDATE()+2), (2, 3, CURDATE(), CURDATE()+3), (3, 4, CURDATE(), CURDATE()+4), (3, 1, CURDATE(), CURDATE()+5), (3, 7, CURDATE(), CURDATE()+6), (4, 4, CURDATE(), CURDATE()+7), (5, 3, CURDATE(), CURDATE()+8), (6, 2, CURDATE(), CURDATE()+7), (6, 1, CURDATE(), CURDATE()+6), (7, 4, CURDATE(), CURDATE()+6), (8, 2, CURDATE(), CURDATE()+5), (8, 1, CURDATE(), CURDATE()+4), (8, 3, CURDATE(), CURDATE()+4), (9, 7, CURDATE(), CURDATE()+3), (9, 1, CURDATE(), CURDATE()+3), (10, 5, CURDATE(), CURDATE()+1), (11, 6, CURDATE(), CURDATE()+2), (11, 2, CURDATE(), CURDATE()+6), (11, 8, CURDATE(), CURDATE()+5), (12, 5, CURDATE(), CURDATE()+4), (13, 7, CURDATE(), CURDATE()+3);
SELECT ID, YEAR(DateOut) AS YearOut FROM Transactions WHERE ID>15 ORDER BY ID;
</source>