Date
To store the date data in the database, use DATE data type. The syntax of DATE is as follows:
DATE
Default literal string format of a DATE value is as follows:
YYYY-DD-MM
- YYYY is four digit number that represent a year, which ranges from 0001 to 9999.
- MM is two digit number that represent a month of a year, which ranges from 01 to 12.
- DD is two digit number that represent a day of the specified month, which ranges from 01 to 31, depending on the month.
In the below example, valid_from column is of type DATE. New record are added to the table by adding date as string literal.
/* Create Table */
CREATE TABLE Product_Prices (
product_id INT NOT NULL,
valid_from DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (
product_id
)
);
/* Insert record to the table */
INSERT INTO product_prices (product_id, valid_from, amount)
VALUES (1, '3219-12-31', 100),
(2, '3200-12-31', 50);Time
TIME data type defines a time of a day based on 24-hour clock. Syntax of the TIME data type is:
TIME[ (fractional_second_precision) ]
fractional_second_precision specifies the number of digits for the fractional part of the seconds. It is an optional argument. Default literal format for a TIME value is
hh:mm:ss[.fractional seconds]
- hh is two digit number that represent the hour with a range from 0 to 23.
- mm is two digit number that represent the minute with a range from 0 to 59.
- ss is two digit number that represent the second with the range from 0 to 59.
- Optional fractional seconds part can be zero to seven digits that has a range from 0 to 9999999.
This example shows use of TIME data. visit_time is of data type TIME.
/* Create Table */
CREATE TABLE Visits (
visit_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR (50) NOT NULL,
phone VARCHAR (25),
store_id INT NOT NULL,
visit_date DATE NOT NULL,
visit_time TIME (0) NOT NULL
);
/* Insert record to the table */
INSERT INTO Visits(customer_name, phone, store_id, visit_date, visit_time)
VALUES
('Rohit Sharma', '9933853', 1467, NOW(), '19:51:00');Date and Time
To store both date and time in the database, use DATETIME data type. Syntax of DATETIME is as follows:
DATETIME(fractional_second_precision)
fractional_second_precision specifies the number of digits for the fractional part of the seconds. It is an optional argument. Default literal format for a DATETIME value is
YYYY-MM-DD hh:mm:ss[.fractional seconds]
- YYYY is four digit number that represent a year, which ranges from 0001 to 9999.
- MM is two digit number that represent a month of a year, which ranges from 01 to 12.
- DD is two digit number that represent a day of the specified month, which ranges from 01 to 31, depending on the month.
- hh is a two digit number that represents the hour. It ranges from 00 to 23.
- mm is a two digit number that represents the minute. It ranges from 00 to 59.
- ss is a two digit number that represents the second. It ranges from 00 to 59.
- Optional fractional seconds part can be zero to seven digits that has a range from 0 to 9999999.
The following statement creates a new table that has a visit_at column whose data type is DATETIME.
/* Create Table */
CREATE TABLE visit (
visit_id INT PRIMARY KEY AUTO_INCREMENT,
person_name VARCHAR (50) NOT NULL,
visit_at DATETIME
);
/* Insert record to the table */
INSERT INTO visit (person_name, visit_at)
VALUES ('Vishal', '3100-06-23 07:30:20');