> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Timestamp Without Time Zone

## **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**

```sql theme={null}
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)

<Info>Fractional digits are the digits after the decimal point ( . )</Info>

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

```sql theme={null}
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:

```sql theme={null}
COMPLETE
INSERT 0 6
```

### Case #2: Display the Table

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

```sql theme={null}
SELECT * FROM visitors;
```

It will return the result set as displayed below:

```sql theme={null}
+--------------+--------------+---------------+-----------------------+
| 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:

```sql theme={null}
SELECT * FROM visitors       
WHERE visitDate = '2022-04-23 13:10:09';
```

We will get the following successful results:

```sql theme={null}
+--------------+--------------+---------------+-----------------------+
| 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.

```sql theme={null}
INSERT INTO visitors (visitorName, visitPurp, visitComp, visitDate)  
VALUES  
    ('Jolly', 'Survey', 'Apple', '2022-01-10 09:12:60');
```

```sql theme={null}
INSERT 0 1

Query returned successfully in 135 msec.
```

Verify the result by running the `select` statement below:

```sql theme={null}
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.

```sql theme={null}
+--------------+--------------+---------------+-----------------------+
| 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).

<Warning>It's important to note that the result type of this operator is different. It produces a timestamp with a time zone.</Warning>

### Syntax

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

```sql theme={null}
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.&#x20;

### Example 1

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

```sql theme={null}
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:

```sql theme={null}
                f                
---------------------------------
 2001-02-16 17:28:30.000000+0000
(1 row)
```

### Example 2

Let's consider from the [visitors](/sql-reference/sql-data-types/timestamp-without-time-zone) table, we wish to retrieve a list of visit dates in the MST time zone. We can achieve this using the following query:

```sql theme={null}
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.

```sql theme={null}
         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)
```