Timestamp Without Time Zone Type
The timestamp data type stores time and date values without a time zone. It represents a fixed time, independent of any time zone or applied globally.
- YYYY: Four-digit year
- MM: One / two-digit month
- DD: One / two-digit day
- HH: One / two-digit hour (valid values from 00 to 23)
- MM: One / two-digit minutes (valid values from 00 to 59)
- SS: One / two-digit seconds (valid values from 00 to 59)
- [.SSSSSS]: Up to six fractional digits (microsecond precision)
Fractional digits are the digits after the decimal point ( . )
Here, we will create a visitor table to store visitor data in an office building. It consists of the visitor’s name, the purpose of the visit, company, time, and date, which uses the Timestamp data type.
The visitors table has been successfully created after executing the above query:
Run the SELECT statement to get all records of the visitors table:
It will return the result set as displayed below:
In the below example, the following statement is used to get records with a specified timestamp:
We will get the following successful results:
The time in timestamp has a standard format, i.e., for minutes only valid for values from 00 to 59.
The example below will insert a new record into the visitors table with a value of 60, which exceeds the standard seconds format.
Verify the result by running the select statement below:
We learned that the seconds are displayed as 00 as 60, which adds 1 minute to the minutes' value.
The AT TIME ZONE operator enables us to convert the input timestamp to the target time zone specified in the query. Additionally, the timestamp you inputted will always be presented in the user's local timezone (currently set as UTC).
It's important to note that the result type of this operator is different. It produces a timestamp with a time zone.
To use the AT TIME ZONE operator, you can follow this syntax:
Here's what each element means:
- input_timestamp: This represents the date and time value you want to convert. The user's time zone is fixed to UTC.
- TIME_ZONE: The target time zone to which the timestamp will be converted.
Suppose we have a timestamp, and we want to convert it into the MST time zone:
The result will be a timestamp with the time zone adjusted to MST:
Let's consider from the visitors table, we wish to retrieve a list of visit dates in the MST time zone. We can achieve this using the following query:
With this query, we obtain a list of two columns: visitDate displays the timestamps without a time zone, and visitDateMST stores the timestamps converted to the MST time zone.
