Timestamp Type

15min
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.
OverviewThe 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.
FormatFormat
|
YYYY-MM-DD[HH:MM:SS]
YYYY-MM-DD[HH:MM:SS]
﻿
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 ( . )
Examples#Case 1: Create a tableHere 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.
Create a table
|
CREATE TABLE visitor  (
    visitorName string,
    visitPurp string,
    visitComp string,
    visitDate timestamp
);

INSERT INTO visitor (visitorName, visitPurp, visitComp, visitDate)
VALUES
('Peter', 'Interview', 'Apple', '2022-01-10 09:12:40'),
('Will', 'Meeting', 'McKesson', '2022-01-29 11:28:02'),
('Max', 'Meeting', 'McKesson', '2022-02-11 10:19:10'),
('Dustin', 'Meeting', 'CVS Health', '2022-03-18 14:24:08'),
('Lizzy', 'Meeting', 'CVS Health', '2022-04-23 13:10:09'),
('Evy', 'Interview', 'Apple', '2022-05-01 08:45:50');
CREATE TABLE visitor  (
    visitorName string,
    visitPurp string,
    visitComp string,
    visitDate timestamp
);

INSERT INTO visitor (visitorName, visitPurp, visitComp, visitDate)
VALUES
('Peter', 'Interview', 'Apple', '2022-01-10 09:12:40'),
('Will', 'Meeting', 'McKesson', '2022-01-29 11:28:02'),
('Max', 'Meeting', 'McKesson', '2022-02-11 10:19:10'),
('Dustin', 'Meeting', 'CVS Health', '2022-03-18 14:24:08'),
('Lizzy', 'Meeting', 'CVS Health', '2022-04-23 13:10:09'),
('Evy', 'Interview', 'Apple', '2022-05-01 08:45:50');
﻿
The visitor table has been successfully created after executing the above query:
Insert data
|
INSERT 0 6

Query returned successfully in 219 msec.
INSERT 0 6

Query returned successfully in 219 msec.
﻿
#Case 2: Display the tableRun the SELECT statement to get all records of the visitor table:
Display the table
|
SELECT * FROM visitor;
SELECT * FROM visitor;
﻿
It will return the result-set as displayed below:
Output
|
+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Peter        | Interview    | Apple         | 2022-01-10 09:12:40   |
| Will         | Meeting      | McKesson      | 2022-01-29 11:28:02   |
| Max          | Meeting      | McKesson      | 2022-02-11 10:19:10   |
| Dustin       | Meeting      | CVS Health    | 2022-03-18 14:24:08   |
| Lizzy        | Meeting      | CVS Health    | 2022-04-23 13:10:09   |
| Evy          | Interview    | Apple         | 2022-05-01 08:45:50   |
+--------------+--------------+---------------+-----------------------+
+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Peter        | Interview    | Apple         | 2022-01-10 09:12:40   |
| Will         | Meeting      | McKesson      | 2022-01-29 11:28:02   |
| Max          | Meeting      | McKesson      | 2022-02-11 10:19:10   |
| Dustin       | Meeting      | CVS Health    | 2022-03-18 14:24:08   |
| Lizzy        | Meeting      | CVS Health    | 2022-04-23 13:10:09   |
| Evy          | Interview    | Apple         | 2022-05-01 08:45:50   |
+--------------+--------------+---------------+-----------------------+
﻿
#Case 3: Look for specific timestampIn the below example, the following statement is used to get records with a specified timestamp:
Display the table
|
SELECT * FROM visitor
WHERE visitDate = '2022-04-23 13:10:09';
SELECT * FROM visitor
WHERE visitDate = '2022-04-23 13:10:09';
﻿
We will get the following successful result:
Output
|
+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Lizzy        | Meeting      | CVS Health    | 2022-04-23 13:10:09   |
+--------------+--------------+---------------+-----------------------+
+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Lizzy        | Meeting      | CVS Health    | 2022-04-23 13:10:09   |
+--------------+--------------+---------------+-----------------------+
﻿
#Case 4: Insert a value that exceeds the standard formatThe 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.
Insert data
|
INSERT INTO visitor (visitorName, visitPurp, visitComp, visitDate)
VALUES
    ('Jolly', 'Survey', 'Apple', '2022-01-10 09:12:60');
INSERT INTO visitor (visitorName, visitPurp, visitComp, visitDate)
VALUES
    ('Jolly', 'Survey', 'Apple', '2022-01-10 09:12:60');
﻿
Insert data
|
INSERT 0 1

Query returned successfully in 135 msec.
INSERT 0 1

Query returned successfully in 135 msec.
﻿
Verify the result by running the select statement below:
Display the table
|
SELECT * FROM visitor WHERE visitorName = 'Jolly';
SELECT * FROM visitor WHERE visitorName = 'Jolly';
﻿
We learned that the second will be 00 as 60 adds 1 minute to the minutes' value.
Output
|
+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Jolly        | Survey       | Apple         | 2022-01-10 09:13:00   |
+--------------+--------------+---------------+-----------------------+
+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Jolly        | Survey       | Apple         | 2022-01-10 09:13:00   |
+--------------+--------------+---------------+-----------------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
Date Type
Interval Type
Docs powered by archbee
TABLE OF CONTENTS
Overview
Format
Examples
#Case 1: Create a table
#Case 2: Display the table
#Case 3: Look for specific timestamp
#Case 4: Insert a value that exceeds the standard format