SQL (postgresql) request to create a table with a date field (YYYY-MM-DD-HH)

Question

SQL (postgresql) request to create a table with a date field (YYYY-MM-DD-HH)

Please tell me the command to create a table where there will be a field with a date and time of the type YYYY-MM-DD-HH. I don't really understand what type of data to use.

For example, for the "Test" table, where there will only be "id" and "date" fields, the query will look like

create table Test( 'id' int not null, 'date' ???? not null );

What to put in place "????" ?

0

postgresql sql дата

Author: Антон, 2019-04-06

Source

2 answers

Postgresql uses the following date/time types:

timestamp (with time zone / without time zone) - with and without a time zone. without by default
date - no time zone
time - time without date
interval - time interval

A query with a date without a locale will look something like this:

create table Test(
'id' int not null,
'date' timestamp not null
);

1

Author: alex, 2019-04-06 19:00:08

score 1 · Accepted Answer

PostgreSQL for time storage, provides data types:

timestamp with microsecond accuracy
date with an accuracy of per day
time for a time without a date

As you can see, there is no built-in format for storing only the date and hour. You can choose from the following approaches:

Timestamp

Use timestamp as is. It's easy to add a small restriction so that you don't accidentally write values that don't within the specified accuracy:

create temp table dt (
  t timestamp check ((date_trunc('hour',t) = t))
);

The added check constraint will effectively prohibit attempts to write something like 2019-04-06 21:56:53 in the field, but will allow 2019-01-01 23:00:00

Two fields

The obvious solution is to make a date type field and a separate field for storing the hour

create temp table dt2 (
    d date,
    h smallint check (h >=0 and h <=23)
);

A variation of this case would be to define and use the composite data type.

Native data type

The Postgresql extension features are sufficient to add a custom data type. However, this is much more difficult, and it can also threaten the stability of your database due to errors in the implementation of the new type and a lot of auxiliary operations such as support for statistics and comparison functions. It is hardly advisable to describe it in detail.