website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Run Oxla in 2 minutes
🏃‍♂️Run Oxla on S3
🛫Multi-node Deployment
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
📈Public Metrics
Docs powered by
Archbee
SQL Reference
SQL Data Types

Timestamp Without Time Zone Type

22min

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

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

Pgsql
|
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:

Pgsql
|
COMPLETE
INSERT 0 6


Case #2: Display the Table

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

Pgsql
|
SELECT * FROM visitors;


It will return the result set as displayed below:

Pgsql
|
+--------------+--------------+---------------+-----------------------+
| 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:

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


We will get the following successful results:

Pgsql
|
+--------------+--------------+---------------+-----------------------+
| 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.

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

Pgsql
|
INSERT 0 1

Query returned successfully in 135 msec.


Verify the result by running the select statement below:

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

Pgsql
|
+--------------+--------------+---------------+-----------------------+
| 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:

Pgsql
|
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:

Pgsql
|
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:

Pgsql
|
                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:

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

Pgsql
|
         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)




Updated 01 Sep 2023
Did this page help you?
PREVIOUS
Date Type
NEXT
Timestamp with Time Zone
Docs powered by
Archbee
TABLE OF CONTENTS
Overview
Format
Examples
Case #1: Create a Table
Case #2: Display the Table
Case #3: Look for a Specific Timestamp
Case #4: Insert a Value That Exceeds the Standard Format
AT TIME ZONE Operator
Syntax
Example 1
Example 2
Docs powered by
Archbee

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2023 Oxla