Code Examples In SQL

Introduction

Date and time management is a crucial aspect of many web applications, whether scheduling events, logging user activity, or displaying localized times for a global audience.
SQL provides powerful data types, functions, and tools for managing DATETIME, TIMESTAMP, Unix timestamps, and timezone information across various database systems.

Get Current Time

Retrieving the current date and time is a fundamental operation in most database-driven applications.
SQL offers various functions to fetch the current system time, depending on the database management system (DBMS) you’re using.

Method:

1// SQL Server
2SELECT GETDATE(); -- Returns current date and time (datetime type)
3SELECT SYSDATETIME(); -- Returns current date and time with higher precision (datetime2 type)
4SELECT CONVERT(TIME, GETDATE()); -- Extracts the current time
5
6// MySQL
7SELECT NOW(); -- Returns current date and time (datetime type)
8SELECT CURDATE(); -- Returns current date (date type)
9SELECT CURTIME(); -- Returns current time (time type)
10
11// PostgreSQL
12SELECT NOW(); -- Returns the current date and time with timezone
13SELECT CURRENT_TIMESTAMP; -- Returns the current date and time with timezone
14SELECT CURRENT_TIME; -- Returns the current time with timezone
15SELECT CURRENT_TIME(0); -- Returns the current time without fractional seconds
16
17// Oracle
18SELECT SYSDATE FROM DUAL; -- Returns the current date and time
19SELECT CURRENT_TIMESTAMP FROM DUAL; -- Returns the current date and time with timezone
20SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; -- Extracts the current time
21
22// SQLite
23SELECT datetime('now'); -- Returns the current date and time (UTC)
24SELECT time('now'); -- Returns the current time (UTC)

Example:

1SELECT NOW();

Result:

1+---------------------+
2| NOW()               |
3+---------------------+
4| 2024-10-11 12:47:33 |
5+---------------------+

Get Unix Timestamp

Unix timestamps are a standard way of representing time as the number of seconds (or milliseconds) elapsed since the Unix epoch, which is January 1, 1970, at 00:00:00 UTC. They are widely used in web development for logging, scheduling, and synchronizing time across systems due to their simplicity and consistency.
SQL provides functions to both convert date and time values to Unix timestamps and retrieve the current Unix timestamp.

Method:

1// SQL Server
2SELECT DATEDIFF(SECOND, '1970-01-01 00:00:00', GETUTCDATE()) AS UnixTimestamp;
3
4// MySQL
5SELECT UNIX_TIMESTAMP() AS UnixTimestamp;
6
7// PostgreSQL
8SELECT EXTRACT(EPOCH FROM NOW())::BIGINT AS UnixTimestamp;
9
10// Oracle
11SELECT (CAST(SYSDATE AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400 AS UnixTimestamp FROM DUAL;
12
13// SQLite
14SELECT strftime('%s', 'now') AS UnixTimestamp;

Example:

1-- MySQL
2SELECT UNIX_TIMESTAMP() AS UnixTimestamp;

Result:

1+---------------+
2| UnixTimestamp |
3+---------------+
4|    1734267088 |
5+---------------+

Construct New Time

Constructing new DATETIME values in SQL is an essential operation in many applications. Whether you’re inserting records into a database, updating timestamps, or performing calculations based on specific date and time components
SQL provides several functions to construct or build DATETIME values from individual components such as year, month, day, hour, minute, and second.

Method:

1// SQL Server
2SELECT CAST('2023-12-25 15:30:00' AS DATETIME) AS NewDateTime;
3
4// MySQL
5SELECT '2023-12-25 15:30:00' AS NewDateTime;
6
7// PostgreSQL
8SELECT TIMESTAMP '2023-12-25 15:30:00' AS NewDateTime;
9
10// Oracle
11SELECT TO_DATE('2023-12-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS') AS NewDateTime FROM DUAL;
12
13// SQLite
14SELECT DATETIME('2023-12-25 15:30:00') AS NewDateTime;

Example:

1-- MySQL
2SELECT '2023-12-25 15:30:00' AS NewDateTime;

Result:

1+---------------------+
2| NewDateTime         |
3+---------------------+
4| 2023-12-25 15:30:00 |
5+---------------------+

Convert Unix timestamp to Readable Date Time

Unix timestamps, representing the number of seconds since the Unix epoch (January 1, 1970, at 00:00:00 UTC), are commonly used in programming for their simplicity and efficiency.
However, they are not human-readable, making it necessary to convert them into a readable date and time format for display, logging, or reporting purposes
This introduction covers how to convert Unix timestamps to readable DateTime values across different database management systems (DBMS), explaining the various methods and providing practical examples.

Method:

1// Use Unix timestamp 1672444800 as an example
2// SQL Server
3SELECT DATEADD(SECOND, 1672444800, '1970-01-01 00:00:00') AS DateTimeValue;
4
5// MySQL
6SELECT FROM_UNIXTIME(1672444800) AS DateTimeValue;
7
8// PostgreSQL
9SELECT TO_TIMESTAMP(1672444800) AS DateTimeValue;
10
11// Oracle
12SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(1672444800, 'SECOND') AS DateTimeValue FROM DUAL;
13
14
15// SQLite
16SELECT datetime(1672444800, 'unixepoch') AS DateTimeValue;

Example:

1-- MySQL
2SELECT FROM_UNIXTIME(1672444800) AS DateTimeValue;

Result:

1+---------------------+
2| DateTimeValue       |
3+---------------------+
4| 2022-12-31 00:00:00 |
5+---------------------+

Convert Date Time to Unix timestamp

Converting date-time objects into Unix timestamps is a common operation, often required for storing time values, performing calculations, or interacting with APIs and databases that use Unix time.
SQL provides functions for converting DateTime values to Unix timestamps, and this introduction covers how to perform this conversion across different database management systems (DBMS).

Method:

1// Use date time 2023-12-15 10:00:00 as an example
2// SQL Server
3SELECT DATEDIFF(SECOND, '1970-01-01 00:00:00', '2023-12-15 10:00:00') AS UnixTimestamp;
4
5// MySQL
6SELECT UNIX_TIMESTAMP('2023-12-15 10:00:00') AS UnixTimestamp;
7
8// PostgreSQL
9SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-12-15 10:00:00')::BIGINT AS UnixTimestamp;
10
11// Oracle
12SELECT (TO_DATE('2023-12-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS') - DATE '1970-01-01') * 86400 AS UnixTimestamp FROM DUAL;
13
14// SQLite
15SELECT strftime('%s', '2023-12-15 10:00:00') AS UnixTimestamp;

Example:

1-- MySQL
2SELECT UNIX_TIMESTAMP('2023-12-15 10:00:00') AS UnixTimestamp;

Result:

1+---------------+
2| UnixTimestamp |
3+---------------+
4|    1702634400 |
5+---------------+

Change Time Zone

Timezones play a critical role in applications that work with global users or time-sensitive data across different regions.
This introduction covers how different database management systems (DBMS) like MySQL, PostgreSQL, and SQL Server provide tools to manage time zone conversions.

Method:

1// SQL Server
2SELECT SYSDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS ConvertedTime;
3// List all time zones
4SELECT * FROM sys.time_zone_info;
5
6// MySQL
7SELECT CONVERT_TZ('2023-12-15 12:00:00', 'UTC', 'America/Los_Angeles') AS ConvertedTime;
8// List all time zones (requires admin privileges):
9mysql_tzinfo_to_sql /usr/share/zoneinfo
10SELECT * FROM mysql.time_zone_name;
11
12// PostgreSQL
13SELECT ('2023-12-15 12:00:00' AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles' AS ConvertedTime;
14
15// Oracle
16SELECT FROM_TZ(CAST(TIMESTAMP '2023-12-15 12:00:00' AS TIMESTAMP), 'UTC') 
17       AT TIME ZONE 'America/Los_Angeles' AS ConvertedTime FROM DUAL;
18
19// SQLite
20SELECT DATETIME('2023-12-15 12:00:00', '+2 hours') AS ConvertedTime;

Example:

1-- MySQL
2SELECT CONVERT_TZ('2023-12-15 12:00:00', 'UTC', 'America/Los_Angeles') AS ConvertedTime;

Result:

1+---------------------+
2| ConvertedTime       |
3+---------------------+
4| 2023-12-15 04:00:00 |
5+---------------------+

Time Addition and Subtraction

Date-time addition and subtraction are essential operations for many time-based applications. SQL provides several functions and operations to perform these tasks efficiently.

Method:

1// SQL Server
2SELECT DATEADD(DAY, 5, GETDATE()) AS AddDays; -- Add 5 days
3SELECT DATEADD(HOUR, -3, GETDATE()) AS SubtractHours; -- Subtract 3 hours
4SELECT DATEDIFF(DAY, '2023-12-10', '2023-12-15') AS DifferenceInDays; -- calculate date difference
5
6// MySQL
7SELECT DATE_ADD(NOW(), INTERVAL 5 DAY) AS AddDays; -- Add 5 days
8SELECT DATE_SUB(NOW(), INTERVAL 3 HOUR) AS SubtractHours; -- Subtract 3 hours
9SELECT DATEDIFF('2023-12-15', '2023-12-10') AS DifferenceInDays; -- calculate date difference
10
11// PostgreSQL
12SELECT NOW() + INTERVAL '5 days' AS AddDays; -- Add 5 days
13SELECT NOW() - INTERVAL '3 hours' AS SubtractHours; -- Subtract 3 hours
14SELECT AGE('2023-12-15', '2023-12-10') AS Difference; -- calculate date difference
15
16// Oracle
17SELECT SYSDATE + 5 AS AddDays FROM DUAL; -- Add 5 days
18SELECT SYSDATE - NUMTODSINTERVAL(3, 'HOUR') AS SubtractHours FROM DUAL; -- Subtract 3 hours
19SELECT ROUND(TO_DATE('2023-12-15', 'YYYY-MM-DD') - TO_DATE('2023-12-10', 'YYYY-MM-DD')) AS DifferenceInDays FROM DUAL;
20
21// SQLite
22SELECT DATETIME('now', '+5 days') AS AddDays; -- Add 5 days
23SELECT DATETIME('now', '-3 hours') AS SubtractHours; -- Subtract 3 hours
24SELECT JULIANDAY('2023-12-15') - JULIANDAY('2023-12-10') AS DifferenceInDays; -- calculate date difference

Example:

1-- MySQL
2SELECT DATE_ADD(NOW(), INTERVAL 5 DAY) AS AddDays; -- Add 5 days
3SELECT DATEDIFF('2023-12-15', '2023-12-10') AS DifferenceInDays; -- calculate date difference

Result:

1+---------------------+
2| AddDays             |
3+---------------------+
4| 2024-12-20 13:18:13 |
5+---------------------+
6
7+------------------+
8| DifferenceInDays |
9+------------------+
10|                5 |
11+------------------+