Timestamp Without Time Zone Type

Overview

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.

Format

YYYY-MM-DD [HH:MM:SS[.SSSSSS]]

• 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 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.

CREATE TABLE visitors  (  
   visitorName TEXT, 
   visitPurp TEXT,
   visitComp TEXT,
   visitDate TIMESTAMP WITHOUT TIME ZONE
);  
INSERT INTO visitors (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 visitors table has been successfully created after executing the above query:

COMPLETE
INSERT 0 6

Case #2: Display the Table

Run the SELECT statement to get all records of the visitors table:

SELECT * FROM visitors;

It will return the result set as displayed below:

+--------------+--------------+---------------+-----------------------+
| 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 a Specific Timestamp

In the below example, the following statement is used to get records with a specified timestamp:

SELECT * FROM visitors       
WHERE visitDate = '2022-04-23 13:10:09';

We will get the following successful results:

+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Lizzy        | Meeting      | CVS Health    | 2022-04-23 13:10:09   |
+--------------+--------------+---------------+-----------------------+

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 visitors table with a value of 60, which exceeds the standard seconds format.

INSERT INTO visitors (visitorName, visitPurp, visitComp, visitDate)  
VALUES  
    ('Jolly', 'Survey', 'Apple', '2022-01-10 09:12:60');

INSERT 0 1

Query returned successfully in 135 msec.

Verify the result by running the select statement below:

SELECT * FROM visitors       
WHERE visitorName = 'Jolly';

We learned that the seconds are displayed as 00 as 60, which adds 1 minute to the minutes’ value.

+--------------+--------------+---------------+-----------------------+
| visitorName  | visitPurp    | visitComp     | visitDate             |
+--------------+--------------+---------------+-----------------------+
| Jolly        | Survey       | Apple         | 2022-01-10 09:13:00   |
+--------------+--------------+---------------+-----------------------+

AT TIME ZONE Operator

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.

Syntax

To use the AT TIME ZONE operator, you can follow this syntax:

SELECT TIMESTAMP 'input_timestamp' AT TIME ZONE 'TIME_ZONE';

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.

Example 1

Suppose we have a timestamp, and we want to convert it into the MST time zone:

SELECT TIMESTAMP '2001-02-16 10:28:30' AT TIME ZONE 'MST';

The result will be a timestamp with the time zone adjusted to MST:

                f                
---------------------------------
 2001-02-16 17:28:30.000000+0000
(1 row)

Example 2

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:

SELECT visitDate, visitDate AT TIME ZONE 'MST' as "visitDateMST" FROM visitors; 

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.

         visitdate          |          visitDateMST           
----------------------------+---------------------------------
 2022-01-10 09:12:40.000000 | 2022-01-10 16:12:40.000000+0000
 2022-01-29 11:28:02.000000 | 2022-01-29 18:28:02.000000+0000
 2022-02-11 10:19:10.000000 | 2022-02-11 17:19:10.000000+0000
 2022-03-18 14:24:08.000000 | 2022-03-18 21:24:08.000000+0000
 2022-04-23 13:10:09.000000 | 2022-04-23 20:10:09.000000+0000
 2022-05-01 08:45:50.000000 | 2022-05-01 15:45:50.000000+0000
(6 rows)