How NOW works in SQL? Best NOW examples

How NOW works in SQL? Best NOW examples

When working with SQL, dealing with dates and times is a crucial aspect of database management. One particularly useful function is NOW(), which allows us to retrieve the current date and time from the system. But how exactly does NOW() work in SQL? Let’s break it down and explore the best examples of its usage.

What Is the NOW() Function in SQL?

The NOW() function in SQL returns the current timestamp, which includes both the date and the time. It’s commonly used in various operations, such as logging events, tracking changes, and filtering records based on timestamps.

Here’s a simple query demonstrating the NOW() function:

SELECT NOW();

This will return something like:

2024-06-10 14:35:42

This output format might vary depending on the database system you are using, but the fundamental principle remains the same.

Which Databases Support NOW()?

Not all SQL database systems use NOW() in the exact same way. Let’s take a look at some common relational databases and their handling of this function:

Database System Equivalent Function
MySQL NOW()
PostgreSQL NOW() or CURRENT_TIMESTAMP
SQL Server GETDATE()
Oracle SYSDATE
SQLite DATETIME('now')

NOW() in Different Use Cases

Let’s explore some practical applications of the NOW() function in SQL.

1. Storing Current Date and Time in a Table

When inserting data into a table, you may want to capture the exact timestamp when the record was created. Here’s how:

INSERT INTO orders (customer_id, total_amount, order_date)
VALUES (123, 49.99, NOW());

This will automatically store the current timestamp in the order_date column.

2. Filtering Data Based on Current Time

You can use NOW() to retrieve records from the last 24 hours:

SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 1 DAY;

3. Calculating Time Differences

If you need to calculate how long ago a certain event happened, you can use TIMESTAMPDIFF() in MySQL:

SELECT TIMESTAMPDIFF(HOUR, order_date, NOW()) AS hours_since_order FROM orders;

Similarly, in PostgreSQL:

SELECT EXTRACT(EPOCH FROM (NOW() - order_date))/3600 AS hours_since_order FROM orders;

4. Using NOW() in Default Column Values

Instead of specifying NOW() manually in INSERT statements, you can set a default value in table creation:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    total_amount DECIMAL(10,2),
    order_date TIMESTAMP DEFAULT NOW()
);

Now, every time a new row is inserted, the order_date column will automatically populate with the current date and time.

Difference Between NOW() and Other Time Functions

SQL provides multiple time-related functions, so it’s helpful to distinguish NOW() from others:

  • CURRENT_DATE: Returns only the current date, without time.
  • CURRENT_TIME: Returns only the current time, without date.
  • CURRENT_TIMESTAMP: Similar to NOW(), but standard across SQL-based databases.
  • SYSDATE: Works in Oracle and may behave slightly differently in session contexts.

Limitations and Considerations

Although NOW() is incredibly useful, there are a few things to keep in mind:

  1. The value of NOW() is constant within a single query execution. If used multiple times in the same statement, it will return the same timestamp.
  2. Time zones matter! Some databases use UTC as their standard, while others rely on the server’s local time.
  3. Performance considerations—while calling NOW() in a filter is efficient, using calculations on the column (e.g., WHERE NOW() - INTERVAL 1 DAY = order_date) might degrade performance if indexes aren’t well optimized.

Conclusion

The NOW() function in SQL is a powerful tool for handling timestamps, logging events, and filtering records based on the current date and time. Whether you’re using it in MySQL, PostgreSQL, SQL Server, or another relational database, understanding how and when to use NOW() effectively can significantly improve your database management.

 

Other interesting article:

How RANDOM works in SQL? Best RANDOM examples