SQL current_timestamp

Created By: chatGPT

In SQL, the CURRENT_TIMESTAMP function is used to retrieve the current date and time from the database server. This can be useful for tracking when a record was created or last updated. It's a standard function available in most SQL database systems.

SELECT CURRENT_TIMESTAMP;

The output of the CURRENT_TIMESTAMP function is typically in the format of 'YYYY-MM-DD HH:MM:SS'. It provides an exact timestamp representing the current moment according to the server's time zone.

SELECT CURRENT_TIMESTAMP AS now;

Using CURRENT_TIMESTAMP in INSERT statements enables you to automatically populate a timestamp field when new records are added. This can help in maintaining an accurate log of when each record was created.

INSERT INTO users (username, created_at) VALUES ('john_doe', CURRENT_TIMESTAMP);

You can also use CURRENT_TIMESTAMP to update records with the most recent timestamp. This is particularly useful in scenarios where you need to log modifications.

UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE username = 'john_doe';

In addition to its standard use, you can manipulate the output of CURRENT_TIMESTAMP. For example, if you want to get a date that is three days from now, you can add an interval.

SELECT CURRENT_TIMESTAMP + INTERVAL '3 days' AS future_date;

When dealing with time zones, be aware that CURRENT_TIMESTAMP uses the time zone of the database server. To ensure consistency across different areas, you might want to consider using CURRENT_TIMESTAMP AT TIME ZONE 'UTC' to always work with Coordinated Universal Time.

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS utc_time;