Математические функции

Получение числа из строки

Числа не всегда хранятся в базе данных как числа. Они могут находиться внутри текста. Посмотри на time_offset таблицы timezone:

SELECT *
  FROM timezone
#timezone_idtime_offset
11UTC+2
22UTC+3
33UTC+4
44UTC+5
55UTC+6
.........

Например, мы хотим получить все часовые зоны со смещением относительно UTC не более 5 часов.

Разобьем задачу на части:

  1. Получим сдвиг относительно UTC числом;
  2. Ограничим 5 часами.

Получим сдвиг относительно UTC числом

Мы знаем, что в поле time_offset данные хранятся в формате UTC+СДВИГ_В_ЧАСАХ. Возьмем подстроку, начиная с 5-го символа:

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4) AS hours
  FROM timezone t
#timezone_idtime_offsethours
11UTC+22
22UTC+33
33UTC+44
44UTC+55
55UTC+66
66UTC+77
77UTC+88
88UTC+1010
99UTC+11
1010UTC+99

Ограничим 5 часами

Получив сдвиг в часах, мы сделали почти всю работу. Осталось лишь добавить WHERE right(t.time_offset, -4) <= 5:

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4) AS hours
  FROM timezone t
 WHERE right(t.time_offset, -4) <= 5
error: operator does not exist: text <= integer

Упс... Что-то пошло не так...

Postgres говорит, что нельзя сравнивать текст с числом.

Для исправления ошибки нужно преобразовать подстроку, полученную right(t.time_offset, -4), к типу данных integer (целые числа). Сделать это можно одним из способов:

  • использовав конструкцию ::
  • вызвав функцию cast()

Конструкция ::

Чтобы преобразовать значение к другому типу данных достаточно после значения поставить :: и написать требуемый тип данных. Например:

SELECT '123'::integer
#int4
1123

Посмотрим на типы данных в нашем запросе:

SELECT pg_typeof (t.time_offset) AS "type time_offset",
       pg_typeof (right(t.time_offset, -4)) AS "type number substring",
       pg_typeof (right(t.time_offset, -4)::integer) AS "type with ::integer",
       pg_typeof ('12'::integer) AS "type '12'::integer",
       pg_typeof (current_timestamp) AS "type current_timestamp"
  FROM timezone t
 LIMIT 1
#type time_offsettype number substringtype with ::integertype '12'::integertype current_timestamp
1texttextintegerintegertimestamp with time zone

Доделаем работу до конца

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4)::integer AS hours
  FROM timezone t
 WHERE right(t.time_offset, -4)::integer <= 5
 ORDER BY hours
#timezone_idtime_offsethours
19UTC+11
21UTC+22
32UTC+33
43UTC+44
54UTC+55

Функция cast

Функция cast делает ровно то же самое. Синтаксис

cast (значение AS тип данных)

Например:

SELECT cast('123' AS integer)
#int4
1123
SELECT t.timezone_id,
       t.time_offset,
       cast(right(t.time_offset, -4) AS integer) AS hours
  FROM timezone t
 WHERE cast(right(t.time_offset, -4) AS integer) <= 5
 ORDER BY hours
#timezone_idtime_offsethours
19UTC+11
21UTC+22
32UTC+33
43UTC+44
54UTC+55

P.S. Функция cast включена в стандарт SQL и доступна в СУБД других производителей. Синтаксис через :: является историческим для PostgreSQL. На практике чаще используют ::, потому что так короче.

ПредыдущаяСледующая