Mastering Date and Time Functions in SQL

Photo by Debby Hudson on Unsplash

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.

Happy Dat(e)ing