sql_func_now

SQL Functions for Current Date and Time

Fetching the Current Timestamp

SQL provides functions to retrieve the current system date and time. These are essential for logging, timestamping records, or performing time-based calculations.

While function names can vary slightly across database systems, standard SQL offers CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME.

Common Functions:

• CURRENT_TIMESTAMP: Returns the current date and time (often including fractional seconds and timezone information, depending on the RDBMS and session settings).
• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.

Database-Specific Variations:

• MySQL: NOW(), CURDATE(), CURTIME()
• PostgreSQL: NOW(), CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME
• SQL Server: GETDATE(), SYSDATETIME(), CURRENT_TIMESTAMP
• Oracle: SYSDATE, CURRENT_TIMESTAMP

It’s generally recommended to use the ANSI SQL standard functions (CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME) for better portability, though system-specific functions might offer more precision or features.

SQL Syntax (Standard)

SELECT CURRENT_TIMESTAMP;

SELECT CURRENT_DATE;

SELECT CURRENT_TIME;

Demonstration Database: Northwind

We’ll use the Northwind sample database context.

Below is a selection from the “Products” table:

ProductID
ProductName
SupplierID
CategoryID
Unit
Price


1
Chais
1
1
10 boxes x 20 bags
18.00


2
Chang
1
1
24 - 12 oz bottles
19.00


3
Aniseed Syrup
1
2
12 - 550 ml bottles
10.00


4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22.00


5
Chef Anton's Gumbo Mix
2
2
36 boxes
21.35

SQL Current Timestamp Example

The following SQL statement selects the product name, its price, and the current timestamp, aliased as QueryTime, for all products. This example uses CURRENT_TIMESTAMP which is widely supported.

SELECT ProductName, Price, CURRENT_TIMESTAMP AS QueryTime
FROM Products;

Practical Application: This is frequently used to record when an order was placed (OrderDate), when a user last logged in (LastLoginDate), or to generate reports that include the time of generation.

Time Zones: Be mindful of time zones. CURRENT_TIMESTAMP might return the time in the database server’s time zone or the session’s time zone. Functions like CURRENT_TIMESTAMP AT TIME ZONE 'UTC' or specific configurations might be needed for consistent time zone handling. Consult your RDBMS documentation.