What is TIMESTAMP() in SQL?

Timestamp in Structured Query Language (SQL) is a data type, that represents the date and time value of any specific record at any specific time (or event). SQL Timestamps can store and work with dates, times, time zones, and AD/BCs.

The Timestamp in SQL allows us to store and perform our operations with both the dates and time values, and that too without any particular time zone usually.

The TIMESTAMP() function in SQL allows us to return any DateTime expression, from any date or any DateTime expression. The TIMESTAMP() function also accepts any date expression and adds time value to it, finally returning the overall DateTime expression.

A timestamp data type can be configured in some SQL databases so that when the database is used in a different time zone, the corresponding date and time are displayed and accepted. One such example is the TIMESTAMPZ data type in PostgreSQL.

Now that we have got a brief idea about the timestamp in SQL let us look at the syntax of the same.

Syntax of TIMESTAMP() function in SQL

There are different syntaxes for the timestamp function in SQL. Any valid TimeStamp data expressions generally consist of a date and time, followed by BC or AD as an option. Let us understand it through the syntax below.

Syntax:

In the above syntax, the TIMESTAMP() function in SQL takes the time or the DateTime expression as a parameter. It returns the DateTime value of the expression (or date) as the output.

Let us look at another type of syntax of the TIMESTAMP() function in SQL.

Syntax:

The TIMESTAMP() function not only accepts the DateTime expression as the input but can even access two date or DateTime values as the arguments. After taking two date-times as input, it adds the first argument to the second and then returns the resultant DateTime value.

Now that we have got a brief idea about the syntax of timestamp in SQL, let us look at the parameter values of the same.

Parameters Value

1. Expression: The expression is a mandatory parameter in the TIMESTAMP() function in SQL. The expression can either be a date or a DateTime expression.
2. Time: The time is an optional parameter in the TIMESTAMP() function in SQL. It is a kind of time value that is added to the expression.

Examples of the Timestamp in SQL

Let us now look at some examples to better understand the TIMESTAMP() function in SQL.

Example 1: Return a DateTime Value Based on the Arguments:

We will pass a DateTime value to our TIMESTAMP() function in this example. Let us see what it returns.

Code:

Output:

Explanation:

We passed a date expression in the above code using the SELECT statement to our timestamp() function.

The TIMESTAMP() function will basically add the default time expression to the timestamp and then return the resultant value.

Example 2: Checking the Current TimeStamp

Code:

Output:

Explanation:

So, we can know the current timestamp by using the current_timestamp command.

Example 3: Extracting Information from Timestamp

There are a few functions in SQL, for example, EXTRACT, which allows us to extract some particular set of information from the timestamp. For instance, we can extract seconds, minutes, hours, days, months, years, etc., from the timestamp.

Let us look at the code for the same.

Code:

Output:

Explanation:

In the above code, we have extracted the day from the timestamp. We have used the EXTRACT keyword for the same.

To extract the month, we can write the below code :

Code:

Output:

Explanation: In the above code, we have extracted the month from the timestamp. We have used the EXTRACT keyword for the same.

Now that we have seen about various examples of TIMESTAMP, let us look at Timestamp vs. Timestampz.

TIMESTAMP VS TIMESTAMPZ

Timestamp and Timestampz have a minor difference. The TIMESTAMPZ can be called similar to TIMESTAMP, but with a TIME ZONE. So, the TIMESTAMPZ has a time zone associated with it. Other than that, both the data types stores the UTC offset of the specified times.

Certain SQL databases allow the modification of a timestamp data type. In these databases, we can mention a timezone for the timestamp. By doing that, whenever the database is used in a different time zone, the corresponding date and time will be displayed and accepted. For the same reason, TIMESTAMPZ is used.

Learn More About a Time and Date Format in SQL

You can learn more about the time and date format in SQL by reading the following article on Scaler Topics:

• Time and Date Format in SQL