Date & Time Functions in SQL: NOW(), DATEDIFF(), EXTRACT() and More

Table of Contents

1. Introduction
2. Importance of Date and Time Functions
3. Common Use Cases
4. NOW() – Get Current Date and Time
5. CURRENT_DATE and CURRENT_TIME
6. DATEDIFF() – Difference Between Dates
7. TIMESTAMPDIFF() in MySQL
8. EXTRACT() – Pull Out Parts of Date/Time
9. DATE_PART() in PostgreSQL
10. DATE_ADD() and DATE_SUB()
11. INTERVAL Keyword Explained
12. DATE_TRUNC() – Truncate to Granularity
13. TO_CHAR() – Format Date into String
14. TO_DATE() – Convert String to Date
15. AGE() in PostgreSQL
16. Real-World Example: Calculate Age
17. Real-World Example: User Signups This Month
18. Best Practices for Handling Date/Time
19. Common Errors and Gotchas
20. Summary and What’s Next

1. Introduction

Working with date and time values is a crucial part of SQL development. Whether it’s filtering records by date, computing durations, or formatting timestamps for reports, SQL offers a rich set of functions for handling temporal data.

2. Importance of Date and Time Functions

These functions allow you to:

• Calculate age, durations, or due dates
• Extract specific parts of a date (year, month, etc.)
• Format date outputs for reports
• Filter rows using dynamic timestamps

3. Common Use Cases

• Find all orders in the last 30 days
• Calculate how many days a user has been active
• Format YYYY-MM-DD into human-readable form
• Compare two timestamps or dates

4. NOW() – Get Current Date and Time

sqlCopyEditSELECT NOW(); -- Returns current date and time

Returns a TIMESTAMP including date and time.

Related Functions:

sqlCopyEditSELECT CURRENT_DATE; -- Only the date
SELECT CURRENT_TIME; -- Only the time

5. CURRENT_DATE and CURRENT_TIME

sqlCopyEditSELECT CURRENT_DATE;  -- Example: '2025-05-12'
SELECT CURRENT_TIME;  -- Example: '14:25:34.000000'

These return server time, not the client’s local time.

6. DATEDIFF() – Difference Between Dates

sqlCopyEditSELECT DATEDIFF('2025-05-12', '2025-04-01'); -- Output: 41

Returns number of days between two dates (MySQL).
In SQL Server: DATEDIFF(unit, start_date, end_date)

sqlCopyEditSELECT DATEDIFF(DAY, '2025-04-01', '2025-05-12'); -- Output: 41

7. TIMESTAMPDIFF() in MySQL

Allows unit-based difference:

sqlCopyEditSELECT TIMESTAMPDIFF(YEAR, '2000-01-01', CURDATE()); -- Age in years
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2025-05-01'); -- Output: 16

8. EXTRACT() – Pull Out Parts of Date/Time

sqlCopyEditSELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

Other fields: MONTH, DAY, HOUR, MINUTE, SECOND, WEEK, DOW (day of week)

9. DATE_PART() in PostgreSQL

Equivalent to EXTRACT():

sqlCopyEditSELECT DATE_PART('month', CURRENT_DATE); -- Output: 5

10. DATE_ADD() and DATE_SUB()

MySQL Example:

sqlCopyEditSELECT DATE_ADD('2025-01-01', INTERVAL 30 DAY);  -- Output: '2025-01-31'
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);       -- Output: one month ago

Used for date arithmetic.

11. INTERVAL Keyword Explained

Used to specify a unit of time:

sqlCopyEditSELECT NOW() + INTERVAL 1 DAY;    -- Tomorrow
SELECT NOW() - INTERVAL 7 HOUR;   -- 7 hours ago

Units: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, etc.

12. DATE_TRUNC() – Truncate to Granularity

(PostgreSQL)

sqlCopyEditSELECT DATE_TRUNC('month', NOW()); -- Returns first day of current month

Useful for monthly or weekly grouping.

13. TO_CHAR() – Format Date into String

(PostgreSQL)

sqlCopyEditSELECT TO_CHAR(NOW(), 'YYYY-MM-DD');   -- '2025-05-12'
SELECT TO_CHAR(NOW(), 'Month DD, YYYY'); -- 'May     12, 2025'

Enables human-friendly date formatting.

14. TO_DATE() – Convert String to Date

sqlCopyEditSELECT TO_DATE('2025-05-12', 'YYYY-MM-DD');

Use when storing or comparing string-based dates.

15. AGE() in PostgreSQL

Calculates age or duration between two timestamps:

sqlCopyEditSELECT AGE(NOW(), '1990-01-01'); -- Output: '35 years 4 months 11 days'

16. Real-World Example: Calculate Age

sqlCopyEditSELECT name,
  TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age
FROM users;

For PostgreSQL:

sqlCopyEditSELECT name, AGE(NOW(), birthdate) AS age
FROM users;

17. Real-World Example: User Signups This Month

sqlCopyEditSELECT COUNT(*) 
FROM users 
WHERE signup_date BETWEEN DATE_TRUNC('month', CURRENT_DATE)
                      AND CURRENT_DATE;

In MySQL:

sqlCopyEditSELECT COUNT(*)
FROM users
WHERE signup_date >= DATE_FORMAT(NOW(), '%Y-%m-01');

18. Best Practices for Handling Date/Time

• Always store timestamps in UTC if possible
• Avoid mixing data types (e.g., comparing date with string)
• Use appropriate functions for timezone-aware systems
• Use indexes on timestamp columns for performance
• Beware of timezone drift if comparing across systems

19. Common Errors and Gotchas

20. Summary and What’s Next

SQL provides powerful functions for working with date and time values. From calculating differences to formatting and extracting parts of a date, mastering these tools is essential for writing intelligent, time-aware queries.