Data types

Contents

Data types#

This page covers the specifics of SQL related to data types.

Date and time#

There are several data types that allow you to describe and operate with date and time.

The following table represents datatypes that allows to operate with date and time in PostgreSQL.

Data Type

Description

Example

DATE

Stores the date (year, month, day) without time.

'2024-07-03'

TIME

Stores the time of day (hours, minutes, seconds) without time zone.

'14:30:57.902629'

TIME WITH TIME ZONE (TIMETZ)

Stores the time of day including the time zone.

'14:30:57.902629+02:00'

TIMESTAMP

Stores both date and time without time zone.

'2024-07-03 14:30:57.902629'

TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)

Stores both date and time including the time zone.

'2024-07-03 14:30:57.902629+02:00'

INTERVAL

Represents a time span or duration (days, hours, minutes, seconds).

'1 day 2 hours 3 minutes'

And the same for clickhouse.

Data Type

Description

Example

Date

Represents a date without time (YYYY-MM-DD format).

2024-09-27

DateTime

Represents a date and time (YYYY-MM-DD HH:MM:SS format).

2024-09-27 14:30:00

DateTime64

Represents a date and time with fractional seconds.

2024-09-27 14:30:00.123456

DateTime32

Similar to DateTime, but limited to 32-bit precision.

2024-09-27 14:30:00

DateTime('timezone')

DateTime with a specified timezone.

2024-09-27 14:30:00 (UTC)

Time

Represents time (HH:MM:SS format) without date.

14:30:00

Interval

Represents a time interval (e.g., days, hours).

INTERVAL 1 DAY

Findout more in the special page.

Json#

PostgreSQL has special types for storing JSON structures: JSON and JSONB. Of course, you can store JSON structures as TEXT in the database, but using these special types provides validation of JSON syntax and allows for JSON-specific operations directly within PostgreSQL.

Find out more on this topic on specific page in postgreSQL documentation.

Consider a table with one column defined as JSONB. The following cell demonstrates the creation and population of such a table.

--postgreSQL
DROP TABLE IF EXISTS json_example;
CREATE TABLE json_example(
    json_col JSONB
);

INSERT INTO json_example (json_col)
VALUES
    ('{"A": 0, "B": 0}'),
    ('{"A": 100}'),
    ('{"B": -50}'),
    ('{"A": {"B": 3}}'),
    ('{"B": -30}'),
    ('{"B": 100}'),
    ('{"B" : 90, "A": -50}')

NOTICE: table "json_example" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 7

Now, to show the advantage over storing as text, let’s get the values of field A from all JSONs.

--postgreSQL
SELECT json_col, json_col->'A' A_field FROM json_example;

SELECT 7
json_col a_field
{'A': 0, 'B': 0} 0
{'A': 100} 100
{'B': -50}
{'A': {'B': 3}} {'B': 3}
{'B': -30}
{'B': 100}
{'A': -50, 'B': 90}-50
Contents