Mastering Date and Time Functions in SQL
Dates and times are the heartbeat of data, pulsing through the veins of databases, enriching our understanding of events, and shaping the way we analyze information. In the realm of SQL, your proficiency in handling date and time data is a vital skill. Fortunately, SQL provides a versatile toolkit of date and time functions to make this task not just manageable, but even enjoyable.
In this blog, we will cover the different data and time functions that are available throughout the SQL dialects.
Lets time in.
Here’s an explanation of common date functions in SQL along with examples:
SYSDATE
Returns the current date and time.
SELECT SYSDATE;
CURRENT_DATE
Returns the current date
SELECT CURRENT_DATE;
CURRENT_TIME
Returns the current time.
SELECT CURRENT_TIME;
CURRENT_TIMESTAMP
Returns the current date and time.
SELECT CURRENT_TIMESTAMP;
DATEADD
Adds a specified number of units (days, months, etc.) to a date.
SELECT DATEADD(DAY, 7, '2023-09-01');
DATEDIFF
Calculates the difference between two dates in specified units (days, months, etc.).
SELECT DATEDIFF(DAY, '2023-09-10', '2023-09-01');
DATEPART
Extracts a specific part of a date (year, month, day, etc.).
SELECT DATEPART(Month, '2023-09-01');
DATE_TRUNC
Truncates a date to a specified level of precision (year, month, day, etc.)
SELECT DATE_TRUNC(MONTH, '2023-09-15');
TO_DATE
Converts a string to a date.
SELECT TO_DATE('2023-09-01', 'YYYY-MM-DD');
LAST_DAY
Returns the last day of the month for a given date.
SELECT LAST_DAY('2023-09-15');
FIRST_DAY
Returns the first day of the month for a given date.
SELECT FIRST_DAY('2023-09-15');
EXTRACT
Extracts a specific part of a date (year, month, day, etc.).
SELECT EXTRACT(YEAR FROM '2023-09-01');
MONTHS_BETWEEN
Calculates the number of months between two dates.
SELECT MONTHS_BETWEEN('2023-12-01', '2023-09-01');
TO_TIMESTAMP
Converts a string to a timestamp.
SELECT TO_TIMESTAMP('2023-09-01 15:30:00', 'YYYY-MM-DD HH24:MI:SS');
TO_CHAR
Converts a date or timestamp to a string with a specified format.
SELECT TO_CHAR('2023-09-01', 'YYYY-MM-DD');
DAYNAME
Returns the name of the day of the week for a given date.
SELECT DAYNAME('2023-09-01');
WEEK
Returns the week number of the year for a given date.
SELECT WEEK('2023-09-01');
TIMESTAMPADD
Adds a specified interval (e.g., days, hours) to a timestamp.
SELECT TIMESTAMPADD(HOUR, 3, '2023-09-01 12:00:00');
TIMESTAMPDIFF
Calculates the difference between two timestamps in specified units (e.g., seconds, minutes).
SELECT TIMESTAMPDIFF(SECOND, '2023-09-01 12:00:00', '2023-09-01 12:30:00');
DATE_FORMAT
Formats a date or timestamp as a string using a specified pattern.
SELECT DATE_FORMAT('2023-09-01 15:30:00', '%Y-%m-%d %H:%i:%s');
TIMEZONE_OFFSET
Returns the time zone offset of a timestamp.
SELECT TIMEZONE_OFFSET('Europe/London', '2023-09-01 15:30:00');
OVERLAPS
Checks if two time intervals overlap.
SELECT
(start_date1, end_date1) OVERLAPS (start_date2, end_date2) AS is_overlap
FROM
events;
This query checks if the time intervals (start_date1, end_date1) and (start_date2, end_date2) overlap for events in a table.
AGE
Calculates the age based on a birthdate and a reference date.
SELECT
AGE('1990-03-15', '2023-09-01') AS age_in_years
This query calculates a person’s age in years based on their birthdate and a reference date.
PERIOD_DIFF
Calculates the number of months between two periods.
SELECT
PERIOD_DIFF(202309, 202301) AS month_difference
This query calculates the difference in months between two periods represented as integers (e.g., 202309 and 202301).
DATE HASH FUNCTIONS
Generates a hash value from a date.
SELECT
DATE_HASH('2023-09-01', 'SHA256') AS date_hash_sha256
This query computes a SHA-256 hash value from a date.
Cool. You now have learnt the functionalities of Date and Time across different dialects
These advanced date and time functions in SQL provide you with even more capabilities to manipulate, calculate, and format date and time data according to your specific needs. Whether you’re dealing with intervals, time zones, or fine-grained timestamp extractions, these functions offer powerful tools for precise data handling.