SQL Standard

SQL Current Timestamp

A timestamp refers to a digital time record for when an event occurs. Timestamps are very common in tools such as logging, file, and usage trackers. Using timestamps allows you to keep a record of when an event occurs. For example, you can record when a file was added, modified, deleted, etc.

In a database, timestamps are most common for determining when a record is added or updated.

This article will discuss using Standard SQL to get the current timestamp, current date, and current DateTime.

SQL Current Timestamp

Let’s discuss how to get the current timestamp in Standard SQL. For this, we can use the current_timestamp function.

The syntax is as shown:

CURRENT_TIMESTAMP();

The function should then return the current timestamp when the query is executed on the server.

NOTE: The timestamp returned by the function is continuous, non-ambiguous, and has exactly 60 seconds per minute.

An example use case is shown below:

SELECT
CURRENT_TIMESTAMP() AS now;

This should return the timestamp as shown:

2022-03-18T18:20:50.239Z

SQL Current DateTime

You can also get the current date and time that a query is executed using the current_datetime function.

The syntax is as shown:

CURRENT_DATETIME([time_zone])

The function takes the target timezone and returns the date-time for that timezone. If the timezone is not specified, the result will be in UTC.

An example usage is as shown:

SELECT
CURRENT_DATETIME() as utc,
CURRENT_DATETIME('Africa/Nairobi') AS nairobi,
CURRENT_DATETIME('+03:00') as utc_add;

We use three different techniques to specify the timezones in the example above.

"utc": "2022-03-18T18:30:39.619901"
"nairobi": "2022-03-18T21:30:39.619901"
"utc_add": "2022-03-18T21:30:39.619901"

SQL Current Date

Using the current_date function, you can also get the current date in the specified timezone using the current_date function. An example usage is as shown:

SELECT
CURRENT_DATE() as utc,
CURRENT_DATE('Africa/Nairobi') AS nairobi,
CURRENT_DATE('+03:00') as utc_add;

The code above should return the output as shown:

"utc": "2022-03-18"
"nairobi": "2022-03-18"
"utc_add": "2022-03-18"

The above returns date type.

Conclusion

This article explores how to get the current timestamp, current DateTime, and date-time in Standard SQL. Learn more about SQL through other articles in this website.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list