ClickHouse now() Function

The now() function in ClickHouse returns the current date and time as a DateTime value. It's commonly used when you need to include the current timestamp in your queries, such as for logging, filtering recent data, or setting default values for timestamp columns.

Syntax

now()

Official ClickHouse Documentation on now()

Example Usage

SELECT now() AS current_timestamp;

-- Using now() in a WHERE clause
SELECT *
FROM events
WHERE event_time > now() - INTERVAL 1 HOUR;

-- Inserting current timestamp
INSERT INTO logs (log_time, message)
VALUES (now(), 'User logged in');

Common Issues

1. Time zone considerations: now() returns the server's local time. Ensure your application and database time zones are aligned for consistent results.

2. Caching behavior: In distributed queries, now() might return slightly different values on different nodes. For exact consistency across a distributed query, consider using a single value obtained from now() and passing it as a parameter.

Best Practices

1. Use now() for dynamic timestamp generation rather than hardcoding current time in your queries.

2. When precise timing is crucial, consider using now64() for microsecond precision.

3. For date-only operations, you can combine now() with toDate(): toDate(now()).

4. In scenarios requiring consistent timestamps across a distributed query, fetch now() once and use it as a parameter in subsequent parts of your query.

Frequently Asked Questions

Q: How does now() differ from today()?
A: now() returns the current date and time as a DateTime value, while today() returns only the current date as a Date value. Use now() when you need time precision and today() when you only need the date.

Q: Can I change the time zone of now()?
A: now() always returns the server's local time. To get the current time in a different time zone, you can use toTimeZone(now(), 'YourTimeZone').

Q: How can I format the output of now()?
A: You can use formatting functions like formatDateTime(). For example: SELECT formatDateTime(now(), '%Y-%m-%d %H:%M:%S').

Q: Is now() affected by daylight saving time (DST)?
A: Yes, now() reflects the server's local time, including DST adjustments if applicable to the server's time zone setting.

Q: How often does the value of now() update within a query?
A: The value of now() is typically constant within a single query execution, ensuring consistency. However, for long-running queries or in distributed environments, you might observe slight differences.