Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
Software Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Date/Time Types

Updated on 2022-06-11 GMT+08:00

Table 1 lists date and time types supported by GaussDB(DWS). For the operators and built-in functions of the types, see Date and Time Processing Functions and Operators.

NOTE:

If the time format of another database is different from that of GaussDB(DWS), modify the value of the DateStyle parameter to keep them consistent.

Table 1 Date/Time types

Name

Description

Storage Space

DATE

In Oracle compatibility mode, it is equivalent to timestamp(0) and records the date and time.

In other modes, it records the date.

In Oracle compatibility mode, it occupies 8 bytes.

In Oracle compatibility mode, it occupies 4 bytes.

TIME [(p)] [WITHOUT TIME ZONE]

Specifies the time of day (no date).

p indicates the precision after the decimal point. The value ranges from 0 to 6.

8 bytes

TIME [(p)] [WITH TIME ZONE]

Specifies time within one day (with time zone).

p indicates the precision after the decimal point. The value ranges from 0 to 6.

12 bytes

TIMESTAMP[(p)] [WITHOUT TIME ZONE]

Specifies the date and time.

p indicates the precision after the decimal point. The value ranges from 0 to 6.

8 bytes

TIMESTAMP[(p)][WITH TIME ZONE]

Specifies the date and time (with time zone). TIMESTAMP is also called TIMESTAMPTZ.

p indicates the precision after the decimal point. The value ranges from 0 to 6.

8 bytes

SMALLDATETIME

Specifies the date and time (without time zone).

The precision level is minute. 31s to 59s are rounded into 1 minute.

8 bytes

INTERVAL DAY (l) TO SECOND (p)

Specifies the time interval (X days X hours X minutes X seconds).

  • l: indicates the precision of days. The value ranges from 0 to 6. To adapt to Oracle syntax, the precision functions are not supported.
  • p: indicates the precision of seconds. The value ranges from 0 to 6. The digit 0 at the end of a decimal number is not displayed.

16 bytes

INTERVAL [FIELDS] [ (p) ]

Specifies the time interval.

  • fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, and MINUTE TO SECOND.
  • p: indicates the precision of seconds. The value ranges from 0 to 6. p takes effect only when fields are SECOND, DAY TO SECOND, HOUR TO SECOND, or MINUTE TO SECOND. The digit 0 at the end of a decimal number is not displayed.

12 bytes

reltime

Relative time interval. The format is:

X years X months X days XX:XX:XX

  • The Julian calendar is used. It specifies that a year has 365.25 days and a month has 30 days. The relative time interval needs to be calculated based on the input value. The output format is POSTGRES.

4 bytes

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
--Create a table:
CREATE TABLE date_type_tab(coll date);

--Insert data:
INSERT INTO date_type_tab VALUES (date '12-10-2010');

-- View data:
SELECT * FROM date_type_tab;
        coll         
---------------------
 2010-12-10 00:00:00
(1 row)

-- Delete the tables:
DROP TABLE date_type_tab;

--Create a table:
CREATE TABLE time_type_tab (da time without time zone ,dai time with time zone,dfgh timestamp without time zone,dfga timestamp with time zone, vbg smalldatetime);

--Insert data:
INSERT INTO time_type_tab VALUES ('21:21:21','21:21:21 pst','2010-12-12','2013-12-11 pst','2003-04-12 04:05:06');

-- View data:
SELECT * FROM time_type_tab;
    da    |     dai     |        dfgh         |          dfga          |         vbg         
----------+-------------+---------------------+------------------------+---------------------
 21:21:21 | 21:21:21-08 | 2010-12-12 00:00:00 | 2013-12-11 16:00:00+08 | 2003-04-12 04:05:00
(1 row)

-- Delete the tables:
DROP TABLE time_type_tab;

--Create a table:
CREATE TABLE day_type_tab (a int,b INTERVAL DAY(3) TO SECOND (4)); 

--Insert data:
INSERT INTO day_type_tab VALUES (1, INTERVAL '3' DAY);

-- View data:
SELECT * FROM day_type_tab;
 a |   b    
---+--------
 1 | 3 days
(1 row)

-- Delete the tables:
DROP TABLE day_type_tab;

--Create a table:
CREATE TABLE year_type_tab(a int, b interval year (6));

--Insert data:
INSERT INTO year_type_tab VALUES(1,interval '2' year);

-- View data:
SELECT * FROM year_type_tab;
 a |    b    
---+---------
 1 | 2 years
(1 row)

-- Delete the tables:
DROP TABLE year_type_tab;

Date Input

Date and time input is accepted in almost any reasonable formats, including ISO 8601, SQL-compatible, and traditional POSTGRES. The system allows you to customize the sequence of day, month, and year in the date input. Set the DateStyle parameter to MDY to select month-day-year interpretation, DMY to select day-month-year interpretation, or YMD to select year-month-day interpretation.

Remember that any date or time literal input needs to be enclosed with single quotes, and the syntax is as follows:

type [ ( p ) ] 'value'

The p that can be selected in the precision statement is an integer, indicating the number of fractional digits in the seconds column. Table 2 shows some possible inputs for the date type.

Table 2 Date input

Example

Description

1999-01-08

ISO 8601 (recommended format). January 8, 1999 in any mode

January 8, 1999

Unambiguous in any date input mode

1/8/1999

January 8 in MDY mode. August 1 in DMY mode

1/18/1999

January 18 in MDY mode, rejected in other modes

01/02/03

  • January 2, 2003 in MDY mode
  • February 1, 2003 in DMY mode
  • February 3, 2001 in YMD mode

1999-Jan-08

January 8 in any mode

Jan-08-1999

January 8 in any mode

08-Jan-1999

January 8 in any mode

99-Jan-08

January 8 in YMD mode, else error

08-Jan-99

January 8, except error in YMD mode

Jan-08-99

January 8, except error in YMD mode

19990108

ISO 8601. January 8, 1999 in any mode

990108

ISO 8601. January 8, 1999 in any mode

1999.008

Year and day of year

J2451187

Julian date

January 8, 99 BC

Year 99 BC

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
--Create a table:
CREATE TABLE date_type_tab(coll date);

--Insert data:
INSERT INTO date_type_tab VALUES (date '12-10-2010');

-- View data:
SELECT * FROM date_type_tab;
        coll         
---------------------
 2010-12-10 00:00:00
(1 row)

-- View the date format:
SHOW datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

-- Configure the date format:
SET datestyle='YMD';
SET

-- Insert data:
INSERT INTO date_type_tab VALUES(date '2010-12-11');

-- View data:
SELECT * FROM date_type_tab;
        coll         
---------------------
 2010-12-10 00:00:00
 2010-12-11 00:00:00
(2 rows)

-- Delete the tables:
DROP TABLE date_type_tab;

Times

The time-of-day types are TIME [(p)] [WITHOUT TIME ZONE] and TIME [(p)] [WITH TIME ZONE]. TIME alone is equivalent to TIME WITHOUT TIME ZONE.

If a time zone is specified in the input for TIME WITHOUT TIME ZONE, it is silently ignored.

For details about the time input types, see Table 3. For details about time zone input types, see Table 4.

Table 3 Time input

Example

Description

05:06.8

ISO 8601

4:05:06

ISO 8601

4:05

ISO 8601

40506

ISO 8601

4:05 AM

Same as 04:05. AM does not affect value

4:05 PM

Same as 16:05. Input hour must be <= 12

04:05:06.789-8

ISO 8601

04:05:06-08:00

ISO 8601

04:05-08:00

ISO 8601

040506-08

ISO 8601

04:05:06 PST

Time zone specified by abbreviation

2003-04-12 04:05:06 America/New_York

Time zone specified by full name

Table 4 Time zone input

Example

Description

PST

Abbreviation (for Pacific Standard Time)

America/New_York

Full time zone name

-8:00

ISO-8601 offset for PST

-800

ISO-8601 offset for PST

-8

ISO-8601 offset for PST

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT time '04:05:06';
   time   
----------
 04:05:06
(1 row)

SELECT time '04:05:06 PST';
   time   
----------
 04:05:06
(1 row)

SELECT time with time zone '04:05:06 PST';
   timetz    
-------------
 04:05:06-08
(1 row)

Special Values

The special values supported by GaussDB(DWS) are converted to common date/time values when being read. For details, see Table 5.

Table 5 Special Values

Input String

Applicable Type

Description

epoch

date, timestamp

1970-01-01 00:00:00+00 (Unix system time zero)

infinity

timestamp

Later than any other timestamps

-infinity

timestamp

Earlier than any other timestamps

now

date, time, timestamp

Start time of the current transaction

today

date, timestamp

Today midnight

tomorrow

date, timestamp

Tomorrow midnight

yesterday

date, timestamp

Yesterday midnight

allballs

time

00:00:00.00 UTC

Interval Input

The input of reltime can be any valid interval in TEXT format. It can be a number (negative numbers and decimals are also allowed) or a specific time, which must be in SQL standard format, ISO-8601 format, or POSTGRES format. In addition, the text input needs to be enclosed with single quotation marks ('').

For details, see Table 6.

Table 6 Interval input

Input

Output

Description

60

2 mons

Numbers are used to indicate intervals. The default unit is day. Decimals and negative numbers are also allowed. Particularly, a negative interval syntactically means how long before.

31.25

1 mons 1 days 06:00:00

-365

-12 mons -5 days

1 years 1 mons 8 days 12:00:00

1 years 1 mons 8 days 12:00:00

Intervals are in POSTGRES format. They can contain both positive and negative numbers and are case-insensitive. Output is a simplified POSTGRES interval converted from the input.

-13 months -10 hours

-1 years -25 days -04:00:00

-2 YEARS +5 MONTHS 10 DAYS

-1 years -6 mons -25 days -06:00:00

P-1.1Y10M

-3 mons -5 days -06:00:00

Intervals are in ISO-8601 format. They can contain both positive and negative numbers and are case-insensitive. Output is a simplified POSTGRES interval converted from the input.

-12H

-12:00:00

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Create a table.
CREATE TABLE reltime_type_tab(col1 character(30), col2 reltime);

-- Insert data.
INSERT INTO reltime_type_tab VALUES ('90', '90');
INSERT INTO reltime_type_tab VALUES ('-366', '-366');
INSERT INTO reltime_type_tab VALUES ('1975.25', '1975.25');
INSERT INTO reltime_type_tab VALUES ('-2 YEARS +5 MONTHS 10 DAYS', '-2 YEARS +5 MONTHS 10 DAYS');
INSERT INTO reltime_type_tab VALUES ('30 DAYS 12:00:00', '30 DAYS 12:00:00');
INSERT INTO reltime_type_tab VALUES ('P-1.1Y10M', 'P-1.1Y10M');

-- View data.
SELECT * FROM reltime_type_tab;
              col1              |                col2                 
--------------------------------+-------------------------------------
 1975.25                        | 5 years 4 mons 29 days
 -2 YEARS +5 MONTHS 10 DAYS     | -1 years -6 mons -25 days -06:00:00
 P-1.1Y10M                      | -3 mons -5 days -06:00:00
 -366                           | -1 years -18:00:00
 90                             | 3 mons
 30 DAYS 12:00:00               | 1 mon 12:00:00
(6 rows)

-- Delete tables.
DROP TABLE reltime_type_tab;

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback