Sql

Date postgresql: PostgreSQL: Documentation: 15: 9.9. Date/Time Functions and Operators

Текущая ДАТА и ВРЕМЯ при добавлении записи в таблицу — efim360.ru

PostgreSQL

Как автоматически проставлять текущую ДАТУ и ВРЕМЯ при добавлении записи в таблицу?

Давайте начнём с того какие вообще типы данных для ДАТЫ и ВРЕМЕНИ существуют:

Типы даты и времени в СУБД PostgreSQL 2022 год

 

PostgreSQL предлагает нам основные 6 вариантов.

  • timestamp without time zone
  • timestamp with time zone
  • date
  • time without time zone
  • time with time zone
  • interval

Все эти варианты могут быть ещё и в виде массивов:

  • timestamp without time zone []
  • timestamp with time zone []
  • date []
  • time without time zone []
  • time with time zone []
  • interval []

А ещё все эти варианты могут иметь точность значений. Можно урезать точность вычислений при помощи круглых скобок.

 

Давайте для примера создадим таблицу и запихнём в неё все базовые вариации:

— Table: a. t_2

 

— DROP TABLE IF EXISTS a.t_2;

 

CREATE TABLE IF NOT EXISTS a.t_2

(

    dt1 timestamp without time zone,

    dt2 timestamp with time zone,

    dt3 date,

    dt4 time without time zone,

    dt5 time with time zone,

    dt6 interval

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

 

ALTER TABLE IF EXISTS a.t_2

    OWNER to postgres;

 

Вот так это может выглядеть в таблице.

Таблица с типами ДАТЫ и ВРЕМЕНИ в интерфейсе pgAdmin — 2022 год

 

Если в таком виде оставим, то ничего толкового не получится. Нужен ещё столбец c первичными ключами и данными. Добавим их.

Таблица с типами ДАТЫ и ВРЕМЕНИ в интерфейсе pgAdmin — добавили первичный ключ и данные — 2022 год

 

Ввод данных

Первичный ключ у нас оформлен автоинкрементом, а данные мы добавляем вручную.

Накинем одну запись в таблицу.

Добавили запись в таблицу где типы даты и времени могут быть null — PostgreSQL

И мы видим, что ни одна дата не проставилась автоматически при вставке данных записи в таблицу PostgreSQL. Что делать?

 

Решение

Чтобы ДАТЫ и ВРЕМЕНА проставлялись автоматически, нужно задать значения в поле DEFAULT

В таблице не заполнено поле DEFAULT для типов ВРЕМЕНИ и ДАТЫ — автоматически НЕ проставляется — PostgreSQL

 

Какие значения нужно задавать?

PostgreSQL предоставляет набор функций, результат которых зависит от текущей ДАТЫ и ВРЕМЕНИ. Все следующие функции соответствуют стандарту SQL и возвращают значения, отражающие время начала текущей транзакции:

  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • CURRENT_TIME(точность)
  • CURRENT_TIMESTAMP(точность)
  • LOCALTIME
  • LOCALTIMESTAMP
  • LOCALTIME(точность)
  • LOCALTIMESTAMP(точность)

Давайте подпихнём нужные варианты ФУНКЦИИ в нужные ДЕФОЛТЫ таблицы

Добавили функции к нужным типам данных даты и времени в PostgreSQL

 

А теперь нужно добавить новую запись

Добавили запись в таблицу где типы даты и времени имеют дефолтные функции — PostgreSQL

И вот в добавленной записи автоматически проставились значения ДАТ и ВРЕМЁН. Теперь нам не нужно париться по высчитыванию дат.

Теперь мы точно будем знать когда запись была добавлена в таблицу.

 

Конечно же это самый «мизер» о датах и временах, но мы успешно решили нашу маленькую задачу.

Итоговый код таблицы:

Итоговый SQL-код таблицы с ДАТАМИ и ВРЕМЕНАМИ — PostgreSQL

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Раздел «8.5. Типы даты/времени» — https://postgrespro.ru/docs/postgresql/14/datatype-datetime

Раздел «9.9.5. Текущая дата/время» — https://postgrespro.ru/docs/postgresql/14/functions-datetime#FUNCTIONS-DATETIME-CURRENT

PostgreSQL | Типы данных

Последнее обновление: 17.03.2018

При определении таблицы для всех ее столбцов необходимо указать тип данных. Тип данных определяет диапазон значений, которые могут храниться в столбце, сколько они будут занимать места в памяти.

PostgreSQL поддерживает богатую палитру различных типов данных, среди которые условно можно разделить на подгруппы: числовые, символьные, логические, дата и время, бинарные и ряд других.

Числовые типы данных

  • serial: представляет автоинкрементирующееся числовое значение, которое занимает 4 байта и может хранить числа от 1 до 2147483647. Значение данного типа образуется путем автоинкремента значения предыдущей строки. Поэтому, как правило, данный тип используется для определения идентификаторов строки.

  • smallserial: представляет автоинкрементирующееся числовое значение, которое занимает 2 байта и может хранить числа от 1 до 32767. Аналог типа serial для небольших чисел.

  • bigserial: представляет автоинкрементирующееся числовое значение, которое занимает 8 байт и может хранить числа от 1 до 9223372036854775807. Аналог типа serial

    для больших чисел.

  • smallint: хранит числа от -32768 до +32767. Занимает 2 байта. Имеет псевдоним int2.

  • integer: хранит числа от -2147483648 до +2147483647. Занимает 4 байта. Имеет псевдонимы int и int4.

  • bigint: хранит числа от -9223372036854775808 до +9223372036854775807. Занимает 8 байт. Имеет псевдоним int8.

  • numeric: хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков после запятой.

    Данный тип может принимать два параметра precision и scale: numeric(precision, scale).

    Параметр precision указывает на максимальное количество цифр, которые может хранить число.

    Параметр scale представляет максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0.

    Например, для числа 23.5141 precision равно 6, а scale — 4.

  • decimal: хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков в дробной части. То же самое, что и numeric.

  • real: хранит числа с плавающей точкой из диапазона от 1E-37 до 1E+37. Занимает 4 байта. Имеет псевдоним float4.

  • double precision: хранит числа с плавающей точкой из диапазона от 1E-307 до 1E+308. Занимает 8 байт. Имеет псевдоним float8.

Примеры использования:


Id SERIAL,
TotalWeight NUMERIC(9,2),
Age INTEGER,
Surplus REAL

Типы для работы с валютой (денежными единицами)

Для работы с денежными единицами определен тип money, который может принимать значения в диапазоне от -92233720368547758.08 до +92233720368547758.07 и занимает 8 байт.

Символьные типы

  • character(n): представляет строку из фиксированного количества символов. С помощью параметра задается задается количество символов в строке. Имеет псевдоним char(n).

  • character varying(n): представляет строку из переменной длины. С помощью параметра задается задается максимальное количество символов в строке. Имеет псевдоним varchar(n).

  • text: представляет текст произвольной длины.

Бинарные данные

Для хранения бинарных данных определен тип bytea. Он хранит данные в виде бинарных строк, которые представляют последовательность октетов или байт.

Типы для работы с датами и временем

  • timestamp: хранит дату и время. Занимает 8 байт. Для дат самое нижнее значение — 4713 г до н.э., самое верхнее значение — 294276 г н.э.

  • timestamp with time zone: то же самое, что и timestamp, только добавляет данные о часовом поясе.

  • date: представляет дату от 4713 г. до н.э. до 5874897 г н.э. Занимает 4 байта.

  • time: хранит время с точностью до 1 микросекунды без указания часового пояса. Принимает значения от 00:00:00 до 24:00:00. Занимает 8 байт.

  • time with time zone: хранит время с точностью до 1 микросекунды с указанием часового пояса. Принимает значения от 00:00:00+1459 до 24:00:00-1459. Занимает 12 байт.

  • interval: представляет временной интервал. Занимает 16 байт.

Распространенные форматы дат:

  • yyyy-mm-dd — 1999-01-08

  • Month dd, yyyy — January 8, 1999

  • mm/dd/yyyy — 1/8/1999

Распространенные форматы времени:

Логический тип

Тип boolean может хранить одно из двух значений: true или false.

Вместо true можно указывать следующие значения: TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’.

Вместо false можно указывать следующие значения: FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘off’, ‘0’.

Типы для представления интернет-адресов

  • cidr: интернет-адрес в формате IPv4 и IPv6. Например,

    192.168.0.1. Занимает от 7 до 19 байт.

  • inet: интернет-адрес в формате cidr/y, где cidr это адрес в формате IPv4 или IPv6, а /y — количество бит в адресе (если этот параметр не указан, то используется 34 для IPv4, 128 для IPv6). Например, 192.168.0.1/24 или 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128. Занимает от 7 до 19 байт.

  • macaddr: хранит MAC-адрес. Занимает 6 байт.

  • macaddr8: хранит MAC-адрес в формате EUI-64. Занимает 8 байт.

Геометрические типы

  • point: представляет точку на плоскости в формате (x,y). Занимает 16 байт.

  • line: представляет линию неопределенной длины в формате {A,B,C}. Занимает 32 байта.

  • lseg: представляет отрезок в формате ((x1,y1),(x2,y2)). Занимает 32 байта.

  • box: представляет прямоугольник в формате ((x1,y1),(x2,y2)). Занимает 32 байта.

  • path: представляет набор содиненных точек. В формате ((x1,y1),...) путь является закрытым (первая и последняя точка соединяются линией) и фактически представляет многоугольник. В формате [(x1,y1),...] путь является открытым Занимает 16+16n байт.

  • polygon: представляет многоугольник в формате ((x1,y1),...). Занимает 40+16n байт.

  • circle: представляет окружность в формате <(x,y),r>. Занимает 24 байта.

Остальные типы данных

  • json: хранит данные json в текстовом виде.

  • jsonb: хранит данные json в бинарном формате.

  • uuid: хранит универсальный уникальный идентификатор (UUID), например, a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. Занимает 32 байта.

  • xml: хранит даные в формате XML.

НазадСодержаниеВперед

Улучшенная обработка дат в запросах PostgreSQL

Деррик Карр

  • постгрес
  • SQL
  • данные
  • сеть

Иногда нам нужна функциональность, недоступная для нашего API ORM. Когда это бывает, мы пишем старый добрый SQL. Это компромисс. Мы отказываемся от удобной даты и вспомогательные методы времени (например, 1.month.ago ) в обмен на добавленный запрос гибкость. К счастью, Postgres предоставляет несколько приемов для сделать работу с датами более плавной. Два из них, которые я считаю наиболее полезными, это тип данных interval и функция date_trunc .

Тип данных interval позволяет нам писать наш SQL запросы таким образом, который отражает то, как мы их произносим. На веб-стороне Rails дает us 30.days.ago ; на стороне базы данных Postgres дает нам интервал '30 дней назад' .

Интервалы нельзя использовать в качестве меток времени, но их можно добавлять и вычитать из. Ниже мы вычитаем 30 дней из текущего времени, чтобы получить список пользователей, созданных за последние 30 дней.

 ВЫБОР *
ОТ пользователей
ГДЕ created_at <= now() - интервал '30 дней'

Практический пример использования

Тип интервала светится, когда есть операции с относительным временем. Его можно использовать с большинством отчетов, основанных на времени, в приложении. Это будет быть полезным задолго до того, как вы развернете свой собственный пакет аналитики. Я видел это используется для переноса метрики North Star в таблицу и для создания пользовательских создание отчетов с помощью Scenic.

Давайте представим, что мы проводим исследование по разработке продукта и хотим опросить пользователей, которые купили что-то через наше приложение электронной коммерции в течение 90 дней регистрации. Мы можем использовать интервал, чтобы легко найти нужных пользователей.

 ВЫБЕРИТЕ *
ОТ пользователей
ПРИСОЕДИНЯЙТЕСЬ к покупкам ON user_id = users.id
ГДЕ Purchases.created_at - users.created < interval '90 days';

Пример простой, но он показывает, что интервал может помочь вам писать чище запросы на основе даты и времени, даже если запросы становятся более сложными.

Функция date_trunc позволяет указать точность даты. Это отбрасывает значения ниже нашей целевой точности, поэтому, если мы хотим указать временную метку к день , затем все остальные поля до секунд обнуляются в возвращаемом значении.

Фрагмент ниже возвращает набор дней, когда новые записи были созданы в произвольная_таблица .

 SELECT DISTINCT ON (созданный_в) date_trunc('день', созданный_в) КАК созданный_в
ИЗ произвольной_таблицы
ГДЕ created_at> '2016-09-01'
ORDER BY created_on DESC;

Функция date_trunc допускает значения, которые не являются очевидными частями меток времени, нравится неделя и квартал . Полный список доступен в Постгрес документы.

Оба интервала и date_trunc помогли мне написать более эффективные SQL-запросы и ответьте на острые вопросы о данных приложения. Иди вперед и пиши бесстрашный (только для чтения) SQL!

Функции даты и времени — Справочник по миграции SQL Server на Aurora PostgreSQL

Совместимость функций Уровень автоматизации AWS SCT / AWS DMS Код действия AWS SCT, индекс Ключевые отличия

Типы данных

PostgreSQL использует другие имена функций.

Использование SQL Server

Функции даты и времени — это скалярные функции, которые выполняют операции с временными или числовыми входными данными и возвращают временные или числовые значения.

Системные значения даты и времени получены из операционной системы сервера, на котором работает SQL Server.

В этом разделе не рассматриваются особенности часового пояса и функции, учитывающие часовой пояс. Дополнительные сведения об обработке часовых поясов см. в разделе Типы данных.

Синтаксис и примеры

В следующей таблице приведены наиболее часто используемые функции даты и времени.

Функция Назначение Пример Результат Комментарии

ПОЛУЧИТЬ ДАТУ и ПОЛУЧИТЬ ДАТУ

Возвращает значение даты и времени, содержащее текущую местную дату или время в формате UTC.

ВЫБЕРИТЕ ПОЛУЧИТЬДАТУ()

2018-04-05 15:53:01.380

DATEPART , ДЕНЬ , МЕСЯЦ и ГОД

Возвращает целочисленное значение, представляющее указанную DATEPART указанной даты.

ВЫБЕРИТЕ МЕСЯЦ(GETDATE()), ГОД(GETDATE())

4, 2018

РАЗНИЦА ДАТЫ

Возвращает целочисленное значение DATEPART границ, которые пересекаются между двумя датами.

ВЫБЕРИТЕ РАЗНИЦУ ДАТЫ(ДЕНЬ, ПОЛУЧИТЬДАТУ(), КОНМЕСЯЦ(ПОЛУЧИТЬДАТУ()))

25

Сколько дней осталось до конца месяца.

ДАТАДОБАВИТЬ

Возвращает значение даты и времени, вычисляемое с интервалом смещения до указанной DATEPART даты.

ВЫБЕРИТЕ ДАТУДОБАВИТЬ(ДЕНЬ, 25, ПОЛУЧИТЬДАТУ())

2018-04-30 15:55:52. 147

ОТЛИТИЕ и ПРЕОБРАЗОВАНИЕ

Преобразует значения даты и времени в строковые литералы и обратно, а также в другие форматы даты и времени.

SELECT CAST (ПОЛУЧИТЬ ДАТУ () КАК ДАТУ) ВЫБЕРИТЕ ПРЕОБРАЗОВАТЬ (VARCHAR(20), GETDATE(), 112)

05.04.2018 20180405

Формат даты по умолчанию. Стиль 112 (ISO) без разделителей.

Дополнительные сведения см. в разделе Функции даты и времени в документации SQL Server .

Использование PostgreSQL

Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) предоставляет очень богатый набор скалярных функций даты и времени; больше, чем SQL Server.

Хотя некоторые функции похожи на функции SQL Server, их функциональность существенно отличается. Будьте особенно осторожны при переносе темпоральной логики на парадигмы Aurora PostgreSQL.

Функции и определение

Функция PostgreSQL Определение функции

ВОЗРАСТ

Вычесть из current_date .

ЧАСЫ_TIMESTAMP

Текущие дата и время.

ТЕКУЩАЯ_ДАТА

Текущая дата.

ТЕКУЩЕЕ_ВРЕМЯ

Текущее время суток.

CURRENT_TIMESTAMP

Текущая дата и время (начало текущей транзакции).

ДАТА_ЧАСТЬ

Получение подполя (эквивалентно извлечению).

DATE_TRUNC

Обрезать до указанной точности.

ЭКСТРАКТ

Получить подполе.

ИСФИНИТ

Проверка конечного интервала.

JUSTIFY_DAYS

Отрегулируйте интервал таким образом, чтобы 30-дневные периоды времени представлялись в виде месяцев.

JUSTIFY_HOURS

Отрегулируйте интервал таким образом, чтобы 24-часовые периоды времени представлялись в виде дней.

JUSTIFY_INTERVAL

Настройка интервала с помощью justify_days и justify_hours с дополнительными корректировками знака.

МЕСТНОЕ ВРЕМЯ

Текущее время суток.

MAKE_DATE

Создать дату из полей года, месяца и дня.

MAKE_INTERVAL

Создать интервал из полей лет, месяцев, недель, дней, часов, минут и секунд.

СДЕЛАТЬ_ВРЕМЯ

Создать время из полей часов, минут и секунд.

MAKE_TIMESTAMP

Создать метку времени из полей года, месяца, дня, часа, минуты и секунды.

MAKE_TIMESTAMPTZ

Создать метку времени с часовым поясом из полей года, месяца, дня, часа, минуты и секунды. Если часовой пояс не указан, используется текущий часовой пояс.

СЕЙЧАС

Текущие дата и время.

STATEMENT_TIMESTAMP

Текущие дата и время.

ВРЕМЯ ДНЯ

Текущие дата и время (аналогично clock_timestamp, но в виде текстовой строки).

TRANSACTION_TIMESTAMP

Текущие дата и время.

TO_TIMESTAMP

Преобразовать эпоху Unix (секунды с 1970-01-01 00:00:00+00) в метку времени.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *