In SQL, dates and times are handled using various data types.
Stores the date only, without any time information.
CREATE TABLE table_name ( my_date DATE );
CREATE TABLE table_name ( my_time TIME );
Stores the time only, without any date information.
CREATE TABLE table_name ( my_datetime DATETIME );
Stores both date and time information.
CREATE TABLE example ( my_timestamp TIMESTAMP );
Similar to DATETIME but often used with automatic updating to the current timestamp when a record is inserted or updated.
-- Insert a date INSERT INTO products (product_added_date) VALUES ('2020-11-11'); -- Insert a time INSERT INTO products (product_added_time) VALUES ('02:20:00'); -- Insert a datetime INSERT INTO products (product_added_date_time) VALUES ('2020-11-11 02:20:00'); -- Insert a timestamp (often automatically updated) INSERT INTO products (product_added_timestamp) VALUES (CURRENT_TIMESTAMP);
Note: when dealing with dates, it's important to use a format that is compatible with the chosen date-related data type.
The format 'YYYY-MM-DD' is commonly used for dates, 'HH:MM:SS' for times, and 'YYYY-MM-DD HH:MM:SS' for datetimes.
SELECT MONTH(date_column) AS month, COUNT(*) AS record_count FROM table_name GROUP BY MONTH(date_column);
SELECT * FROM table_name ORDER BY date_column DESC;