Mastering MySQL Timestamps

Timestamps in MySQL are used to represent date and time values, and they can store information with various levels of precision. MySQL provides several data types for working with timestamps, including DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

Let's focus on the two most commonly used timestamp data types in MySQL: DATETIME and TIMESTAMP. These types store both date and time information, but they have some differences in behavior.

1. DATETIME:

The DATETIME data type stores date and time values in the format 'YYYY-MM-DD HH:MM:SS'.

It does not automatically convert stored values to the server's time zone. The values are stored as provided.

You can specify a time zone when inserting data into a DATETIME column, but the time zone information is not used for calculations.

Example: '2023-09-03 14:30:00'

2. TIMESTAMP:

The TIMESTAMP data type also stores date and time values in the format 'YYYY-MM-DD HH:MM:SS'.

Unlike DATETIME, TIMESTAMP values are converted to the server's time zone for storage and retrieval. This allows for easier handling of time zone-related issues.

When you insert data into a TIMESTAMP column, MySQL will convert it to the server's time zone before storing it.

Example: '2023-09-03 14:30:00'

Inserting Timestamps:

To insert timestamp values into a MySQL table, you can use the INSERT INTO statement. Here's an example using the DATETIME data type:

INSERT INTO your_table (your_datetime_column) VALUES ('2023-09-03 14:30:00');

And for the TIMESTAMP data type:

INSERT INTO your_table (your_timestamp_column) VALUES ('2023-09-03 14:30:00');

Querying Timestamps:

You can query timestamp data using the SELECT statement. Here are some common operations:

Select All Rows: 

SELECT * FROM your_table;

Filter by Timestamp Range:

SELECT * FROM your_table WHERE your_timestamp_column BETWEEN '2023-09-01 00:00:00' AND '2023-09-03 23:59:59';

Extract Date or Time Components:

SELECT DATE(your_datetime_column) AS just_date FROM your_table;
SELECT TIME(your_datetime_column) AS just_time FROM your_table;

Calculate Time Differences:

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) AS time_difference_seconds FROM your_table;

Order by Timestamp:

SELECT * FROM your_table ORDER BY your_timestamp_column ASC;  -- ASC for ascending, DESC for descending

Group by Date:

SELECT DATE(your_datetime_column) AS date, COUNT(*) AS count FROM your_table GROUP BY date;

Timestamp functions:

These functions can be used to extract information from timestamps, perform calculations, and format the results. Here are some commonly used timestamp functions in MySQL:

1. NOW() or CURRENT_TIMESTAMP():

Returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'.

SELECT NOW();

2. DATE():

Extracts the date part from a datetime or timestamp value.

SELECT DATE('2023-09-03 14:30:00'); 
// returns '2023-09-03'.

3. TIME():

Extracts the time part from a datetime or timestamp value.

SELECT TIME('2023-09-03 14:30:00'); 
// returns '14:30:00'.

4. YEAR():

Extracts the year from a date or datetime value.

SELECT YEAR('2023-09-03'); 
// returns 2023

5. MONTH():

Extracts the month from a date or datetime value.

SELECT MONTH('2023-09-03'); 
// returns 9.

6. DAY():

Extracts the day of the month from a date or datetime value.

SELECT DAY('2023-09-03'); 
// returns 3.

7. HOUR(), MINUTE(), SECOND():

Extract the respective components (hour, minute, second) from a time or datetime value.

SELECT HOUR('2023-09-03 10:20:50'); 
// returns 10.
SELECT MINUTE('2023-09-03 10:20:50'); 
// returns 20.
SELECT SECOND('2023-09-03 10:20:50'); 
// returns 50.

8. DATE_FORMAT():

Allows you to format a date or datetime value as a string using custom formatting.

date: The date or datetime value you want to format.

format: A string specifying the desired format for the output. This string can include special format codes that represent various components of the date and time (e.g., %Y for the year, %m for the month, %d for the day, %H for the hour, %i for the minute, %s for the second, etc.).

DATE_FORMAT(date, format)
SELECT DATE_FORMAT('2023-09-03', '%M %d, %Y'); 
// returns 'September 03, 2023'.

9. TIMESTAMPDIFF():

Calculates the difference between two datetime or timestamp values and returns the result in a specified unit (e.g., seconds, minutes, hours).

SELECT TIMESTAMPDIFF(SECOND, '2023-09-03 14:30:00', '2023-09-03 15:30:00'); 
// returns 3600 (1 hour).

10. TIMESTAMPADD():

Adds a specified interval to a datetime or timestamp value.

SELECT TIMESTAMPADD(MINUTE, 30, '2023-09-03 14:30:00'); 
// adds 30 minutes to the timestamp.

11. DAYNAME() and MONTHNAME():

Returns the name of the day or month from a date or datetime value.

SELECT DAYNAME('2023-09-03'); 
// returns 'Saturday'.
SELECT MONTHNAME('2023-09-03'); 
// returns 'September'.