You will understand how to use the timestamp data type in order to easily use the timestamp value when creating or displaying a record in a database table.
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.
⛔️ We don’t support time zones yet.
- 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 ( . )
#Case 1: Create a table
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 visitor table has been successfully created after executing the above query:
#Case 2: Display the table
Run the SELECT statement to get all records of the visitor table:
It will return the result-set as displayed below:
#Case 3: Look for specific timestamp
In the below example, the following statement is used to get records with a specified timestamp:
We will get the following successful result:
#Case 4: Insert a value that exceeds the standard format
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 visitor table with a value of 60 that exceeds the standard format of seconds.
Verify the result by running the select statement below:
We learned that the second will be 00 as 60 adds 1 minute to the minutes' value.