Даты и время являются весьма непростыми объектами:

• месяца содержат различное число дней;
• года бывают високосными и нет;
• существуют различные временне зоны;
• часы, минуты, дни используют различные системы счисления.

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

Совсем краткое резюме для смартфоночиталей: на больших объемах данных используем только POSIXct с дробными долями секунд. Будет хорошо, понятно, быстро.

Является продолжением серии предыдущих публикаций.

Стандарты для специфицирования дат и времени

ISO 8601 Data elements and interchange formats Information interchange Representation of dates and times is an international standard covering the exchange of date- and time-related data.

Базовые методы для работы с датой

Дата

Sys.Date()print("-----")x <- as.Date("2019-01-29") # в UTCprint(x)tz(x)str(x)dput(x)print("-----")dput(as.Date("1970-01-01")) # ! origin

## [1] "2021-04-29"## [1] "-----"## [1] "2019-01-29"## [1] "UTC"##  Date[1:1], format: "2019-01-29"## structure(17925, class = "Date")## [1] "-----"## structure(0, class = "Date")

Нестандартный формат даты при инициализации должен специфицироваться специально

as.Date("04/20/2011", format = "%m/%d/%Y")

## [1] "2011-04-20"

Время

В R применяются два базовых типа времени: POSIXct и POSIXlt.
Внешние представления POSIXct и POSIXlt выглядят похожими. А внутренние?

z <- Sys.time()glue("Внешнее представление",      "POSIXct - {z}",      "POSIXlt - {as.POSIXlt(z)}", "---", .sep = "\n")glue("Внутреннее представление",      "POSIXct - {capture.output(dput(z))}",      "POSIXlt - {paste0(capture.output(dput(as.POSIXlt(z))), collapse = '')}",     "---", .sep = "\n")# Получение отдельных элементов даты/времени базовыми средствамиglue("Год: {year(z)} \nМинуты: {minute(z)}\nСекунды: {second(z)}\n---")

## Внешнее представление## POSIXct - 2021-04-29 15:18:04## POSIXlt - 2021-04-29 15:18:04## ---## Внутреннее представление## POSIXct - structure(1619698684.50764, class = c("POSIXct", "POSIXt"))## POSIXlt - structure(list(sec = 4.50764489173889, min = 18L, hour = 15L,     mday = 29L, mon = 3L, year = 121L, wday = 4L, yday = 118L,     isdst = 0L, zone = "MSK", gmtoff = 10800L), class = c("POSIXlt", "POSIXt"), tzone = c("", "MSK", "MSD"))## ---## Год: 2021 ## Минуты: 18## Секунды: 4## ---

Сразу делаем заключение, что для серьезной работы с данными (более 10 строк с временем), про POSIXlt забываем как про страшный сон.

POSIXct по своей сути является оберткой для unixtimestamp, количество секунд (миллисекунд) с некоей нулевой точки (обычно за 0 полагают 01.01.1970). Делаем ставку в работе именно на него.

Полезный инструмент online преобразование времени в unixtimestamp:

• Epoch Unix Time Stamp Converter
• Epoch & Unix Timestamp Conversion Tools
• currentDate / Time in Millisecondsmillis

Sys.time()z <- 1548802400as.POSIXct(z, origin = "1970-01-01")                # localas.POSIXct(z, origin = "1970-01-01", tz = "UTC")    # in UTC

## [1] "2021-04-29 15:18:04 MSK"## [1] "2019-01-30 01:53:20 MSK"## [1] "2019-01-29 22:53:20 UTC"

Работа с долями секунды

Корни вопроса идут от типовой задачи анализу логов. Для быстрых событий недостаточно секундного разрешения и тут появляются вариации. Время в логе может фиксироваться:

• по рекомендациям ISO, с долями секунд в виде дробной части (ISO 8601-2019);
• с какими-нибудь другими разделителями;
• как отдельное поле.

Объекты класса POSIXct могут хранить и проводить вычисления с дробными секундами, но по умолчанию при выводе на печать дробные части округляются из-за чего могут возникнуть надуманные ограничения. Проверяем и смотрим:

x <- ymd_hms("2014-09-24 15:23:10")xx + 0.5x + 0.5 + 0.6options(digits.secs=5)x + 0.45756options(digits.secs=0)x

## [1] "2014-09-24 15:23:10 UTC"## [1] "2014-09-24 15:23:10 UTC"## [1] "2014-09-24 15:23:11 UTC"## [1] "2014-09-24 15:23:10.45756 UTC"## [1] "2014-09-24 15:23:10 UTC"

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

options(digits.secs=5)# generate datadf <- data.frame(  timestamp = as_datetime(    round(runif(20, min = now() - seconds(10), max = now()), 0),     tz ="Europe/Moscow")) %>%  mutate(ms = round(runif(n(), 0, 999), 0)) %>%  mutate(value = round(runif(n(), 0, 100), 0))dput(df)# сортируем "в лоб"df %>%  arrange(timestamp, ms)options(digits.secs=0)

## structure(list(timestamp = structure(c(1619698677, 1619698680, ## 1619698676, 1619698682, 1619698675, 1619698682, 1619698679, 1619698679, ## 1619698684, 1619698683, 1619698684, 1619698677, 1619698682, 1619698683, ## 1619698675, 1619698676, 1619698685, 1619698681, 1619698683, 1619698681## ), class = c("POSIXct", "POSIXt"), tzone = "Europe/Moscow"), ##     ms = c(418, 689, 729, 108, 226, 843, 12, 370, 5, 581, 587, ##     691, 102, 79, 640, 284, 241, 85, 329, 936), value = c(63, ##     44, 63, 45, 29, 34, 80, 85, 42, 76, 94, 89, 34, 80, 1, 66, ##     29, 81, 15, 98)), class = "data.frame", row.names = c(NA, ## -20L))

# "умное" преобразование# [magrittr aliases](http://personeltest.ru/aways/magrittr.tidyverse.org/reference/aliases.html)df2 <- df %>%  mutate(timestamp = timestamp + ms/1000) %>%  # mutate_at("timestamp", ~`+`(. + ms/1000)) %>%  select(-ms)df2 %>% arrange(timestamp)

# сравним подходыdt <- as.data.table(df2)bench::mark(  naive = dplyr::arrange(df, timestamp, ms),  smart = dplyr::arrange(df2, timestamp),  dt = dt[order(timestamp)],  check = FALSE,  relative = TRUE,  min_iterations = 1000)

## # A tibble: 3 x 6##   expression   min median `itr/sec` mem_alloc `gc/sec`##   <bch:expr> <dbl>  <dbl>     <dbl>     <dbl>    <dbl>## 1 naive       11.9   11.8      1         1.06     1   ## 2 smart       11.1   11.0      1.06      1        1.06## 3 dt           1      1       11.6     494.       1.22

Парсинг данных с миллисекундами.

data <- c("05102019210003657", "05102019210003757", "05102019210003857")dmy_hms(stri_c(stri_sub(data, to = 14L), ".", stri_sub(data, from = 15L)), tz = "Europe/Moscow")# Измерение скорости различных вариантовdata2 <- data %>%  sample(10^6, replace = TRUE)bench::mark(  stri_sub = stri_c(stri_sub(data2, to = 14L), ".", stri_sub(data2, from = 15L)),  stri_replace = stri_replace_first_regex(data2, pattern = "(^.{14})(.*)", replacement = "$1.$2"),  re2_replace = re2_replace(data2, pattern = "(^.{14})(.*)", replacement = "\\1.\\2", parallel = TRUE))

## [1] "2019-10-05 21:00:03 MSK" "2019-10-05 21:00:03 MSK"## [3] "2019-10-05 21:00:03 MSK"## # A tibble: 3 x 6##   expression        min   median `itr/sec` mem_alloc `gc/sec`##   <bch:expr>   <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl>## 1 stri_sub        214ms    222ms      4.10   22.89MB     5.47## 2 stri_replace    653ms    653ms      1.53    7.63MB     0   ## 3 re2_replace     409ms    413ms      2.42   15.29MB     1.21

Пакет lubridate

x <- ymd(20101215)print(x)class(x)

## [1] "2010-12-15"## [1] "Date"

Магия lubridate

ymd(20101215) == mdy("12/15/10")

## [1] TRUE

df <- tibble(first = c("Иван", "Петр", "Алексей"),             last = c("Иванов", "Петров", "Сидоров"),             birthday_str = c("31-10-06", "2/4/2007", "1 June, 2005")) %>%  mutate(birthday = dmy(birthday_str))df

А что делать, если время может поступать в частично обрезанном формате?

# управляем отображением форматов парсинга в lubridateoptions(lubridate.verbose = TRUE)# базовый формат даты: д.м.гdf <- tibble(time_str = c("08.05.19 12:04:56", "09.05.19 12:05", "12.05.19 23"))lubridate::dmy_hms(df$time_str, tz = "Europe/Moscow")print("---------------------")lubridate::dmy(df$time_str, tz = "Europe/Moscow")

## [1] "2019-05-08 12:04:56 MSK" NA                       ## [3] NA                       ## [1] "---------------------"## [1] NA NA NA

Разрешим вариативность определенной глубины

# управляем отображением форматов парсинга в lubridateoptions(lubridate.verbose = TRUE)lubridate::dmy_hms(df$time_str, truncated = 3, tz = "Europe/Moscow")

## [1] "2019-05-08 12:04:56 MSK" "2019-05-09 12:05:00 MSK"## [3] "2019-05-12 23:00:00 MSK"

# управляем отображением форматов парсинга в lubridateoptions(lubridate.verbose = TRUE)# базовый формат даты: д.м.гdf <- tibble(date_str = c("08.05.19", "9/5/2019", "2019-05-07"))

Пробуем провести конвертацию

# пробуем первый вариантglimpse(dmy(df$date_str))print("---------------------")# пробуем второй вариантglimpse(ymd(df$date_str))print("---------------------")

##  Date[1:3], format: "2019-05-08" "2019-05-09" NA## [1] "---------------------"##  Date[1:3], format: "2008-05-19" NA "2019-05-07"## [1] "---------------------"

Что делать? Вариант, конечно, ужасен, но что-то можно поделать.

df %>%  mutate(date = dplyr::coalesce(dmy(date_str), ymd(date_str)))

df1 <- dfdf1$date <- dmy(df1$date_str)idx <- is.na(df1$date)print("---------------------")idxdf1$date[idx] <- ymd(df1$date_str[idx])print("---------------------")df1

## [1] "---------------------"## [1] FALSE FALSE  TRUE## [1] "---------------------"

Еще пакеты

Еще пакеты на "посмотреть" и поизучать:

• anytime
• clock

Арифметические операции с POSIXct

Разность

options(lubridate.verbose = FALSE)date1 <- ymd_hms("2011-09-23-03-45-23")date2 <- ymd_hms("2011-10-03-21-02-19")# какова разница между этими датами?as.numeric(date2) - as.numeric(date1) # как мы помним, разница в секундах(date2 - date1) %>% dput()difftime(date2, date1)difftime(date2, date1, unit="mins")difftime(date2, date1, unit="secs")

## [1] 926216## structure(10.7200925925926, class = "difftime", units = "days")## Time difference of 10.72009 days## Time difference of 15436.93 mins## Time difference of 926216 secs

Периоды

date1 <- ymd_hms("2019-01-30 00:00:00")date1date1 - days(1)date1 + days(1)date1 + days(2)

## [1] "2019-01-30 UTC"## [1] "2019-01-29 UTC"## [1] "2019-01-31 UTC"## [1] "2019-02-01 UTC"

А теперь более сложный пример добавляем месяцы

date1 - months(1)date1 + months(1) # УПС!!!

## [1] "2018-12-30 UTC"## [1] NA

Есть выход. Но операции не коммутативны, это надо помнить.

date1 %m-% months(1)date1 %m+% months(1)date1 %m+% months(1) %m-% months(1)

## [1] "2018-12-30 UTC"## [1] "2019-02-28 UTC"## [1] "2019-01-28 UTC"

Нюансы временных зон

date1 <- ymd_hms("2019-01-30 01:00:00")date1 %T>% print() %>% dput()with_tz(date1, tzone = "Europe/Moscow") %T>% print() %>% dput()force_tz(date1, tzone = "Europe/Moscow") %T>% print() %>% dput()

## [1] "2019-01-30 01:00:00 UTC"## structure(1548810000, class = c("POSIXct", "POSIXt"), tzone = "UTC")## [1] "2019-01-30 04:00:00 MSK"## structure(1548810000, class = c("POSIXct", "POSIXt"), tzone = "Europe/Moscow")## [1] "2019-01-30 01:00:00 MSK"## structure(1548799200, class = c("POSIXct", "POSIXt"), tzone = "Europe/Moscow")

Работа только с временми значениями

Что делать, если у нас есть только время, а даты не указаны? Не проблема, нам поможет пакет hms. Такие данные представляются как периоды.

hms_str <- "03:22:14"as_hms(hms_str)dput(as_hms(hms_str))print("-------")x <- as_hms(hms_str) * 15xstr(x)# seconds_to_period(period_to_seconds(x))seconds_to_period(x) %T>% dput() %>% print()

## 03:22:14## structure(12134, units = "secs", class = c("hms", "difftime"))## [1] "-------"## Time difference of 182010 secs##  'difftime' num 182010##  - attr(*, "units")= chr "secs"## new("Period", .Data = 30, year = 0, month = 0, day = 2, hour = 2, ##     minute = 33)## [1] "2d 2H 33M 30S"

БД и временне данные

Одна из больших засад при работе с временнми данными в БД неизвестность или неполная осведомленность о механике и логике работы конкретных таблиц. Не всегда есть возможность посмотреть запросы по которым они строились или же текст функций.
В современных БД (далее будем подразумевать Clickhouse) время, как правило, хранится как unixtimestamp в UTC. Ну или возможны иные варианты, но все они крутятся вокруг количества единиц времени относительно некоей реперной точки.

Потенциальные сложности и засады:

• При запросе у БД колонки времени под ее капотом может происходить масса метаморфоз. БД сериализует timestamp, при этом могут оказать свое влияние параметры временных зон из БД, ОС, поля, смежного поля, переменных окружения.
• При получении данных на клиентской стороне вмешивается драйвер (серия драйверов и врапперов). При развертывании времени замешивается логика драйверов, параметры локали ОС, языковые и временные параметры среды, значение переменных окружения и отражение лунного света в болоте.
• В поле unixtimestamp разработчики могут помещать отнюдь не UTC время, а московское. Или иное (сюрприз!).
• В БД может быть агрегация и партиционирование по дате, вычисляемой на основании поля timestamp. В силу расхождения в трактовке временных зон, данные за день Х вполне могут уехать в партиции X-1 или X+1, что необходимо учитывать при построении быстрого запроса к БД.

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

Чтобы избежать этого и параллельно получить еще массу преимуществ достаточно перейти на ручное управление.
Суть заключается в переводе дат в числовой формат на стороне базы и обратное преобразование во время (там, где надо) на стороне клиента. Такое решение не сильно обременительно, зато дает дает массу преимуществ:

• полный контроль реальных временнх меток на всех этапах, включая выявлении косяков разработчиков и специфики настройки БД;
• возможность сверки реально получаемы показателей с ожидаемыми;
• прецизионное управление временными зонами для корректной трактовки;
• корректное преобразование времени в даты (с учетом таймзон);
• схождение суточных агрегатов;
• возможность интеграции дробных долей секунд в единый double;
• сокращение временнх затрат на сериализацию и передачу по сети;
• общее увеличение производительности.

Трюк по экономии памяти и времени исполнения без потери информации

-- диалект ClickHouseSELECT DISTINCT    store, pos,    timestamp, ms,    concat(toString(store), '-', toString(pos)) AS pos_uid,    toFloat64(timestamp) + (ms / 1000)          AS timestamp

flog.info(paste("SQL query:", sql_req))tic("Загрузка из CH")raw_df <- dbGetQuery(conn, stri_encode(sql_req, to = "UTF-8")) %>%  mutate_if(is.character, `Encoding<-`, "UTF-8") %>%  as_tibble() %>%  mutate_at(vars(timestamp), anytime::anytime, tz = "Europe/Moscow") %>%  mutate_at("event", as.factor)flog.info(capture.output(toc()))DBI::dbDisconnect(conn)

Хелпер для детального анализа занимаемой data.frame памятью

# сводка по объемам данныхdf -> as_tibble(_df) %>%  map(pryr::object_size) %>%   unlist() %>%   enframe() %>%   arrange(desc(value)) %>%  mutate_at("value", fs::as_fs_bytes) %>%  mutate(ratio = formattable::percent(value / sum(value), 2)) %>%  add_row(name = "TOTAL", value = sum(.$value))

Повторно полезные ссылки по форматам и калькуляторам, необходимым при анализе путей следования дат в ИС и БД

• Epoch & Unix Timestamp Conversion Tools
• currentdate/time in millisecondsmillis
• Functions for working with dates and times

Форматирование дат

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

Привязка к рабочим неделям

df <- seq.Date(from = as.Date("2021-01-01"),                to = as.Date("2021-05-31"),                by = "2 days") %>%  # sample(20, replace = FALSE) %>%  tibble(date = .)

# формируем композитное представление год/месяц/номер недели# способ 1df %>%  mutate(month_num = stri_c(lubridate::year(date),                             sprintf("%02d", lubridate::month(date)),                             sep = "/"),         week_num = stri_c(lubridate::isoyear(date),                            sprintf("%02d", lubridate::isoweek(date)),                            sep = "/")  )

# формируем композитное представление год/месяц/номер недели# способ 2, заодно добавим день недели# особое внимание обращаем, что текстовые поля генерятся согласно текущей локали!!!df %>%  mutate(month_num = format(date, "%Y/%m (%a) ISO week %V"))

# формируем композитное представление год/месяц/номер недели# способ 3, заодно добавим день недели# хелпер по преобразованию формата strptime (ISO 8601) в ICU# https://man7.org/linux/man-pages/man3/strptime.3.htmlstri_datetime_fstr("%Y/%m (%a) week %V")# ggthemes::tableau_color_pal("Tableau 20")(20) %>% scales::show_col()# особое внимание обращаем, что мы можем управлять локалью самостоятельно!!!df %>%  mutate(    month_num_ru = stri_datetime_format(      date, "yyyy'/'MM' ('ccc') week 'ww", locale = "ru", tz = "UTC"),    month_num_en = stri_datetime_format(      date, "yyyy'/'MM' ('ccc') week 'ww", locale = "en", tz = "UTC"))

Дни недели

Пишем дни недели в различных локалях. Не зависит от платформы исполнения.

stri_datetime_format(today(), "LLLL", locale="ru@calendar=Persian")stri_datetime_format(today(), "LLLL", locale="ru@calendar=Indian")stri_datetime_format(today(), "LLLL", locale="ru@calendar=Hebrew")stri_datetime_format(today(), "LLLL", locale="ru@calendar=Islamic")stri_datetime_format(today(), "LLLL", locale="ru@calendar=Coptic")stri_datetime_format(today(), "LLLL", locale="ru@calendar=Ethiopic")stri_datetime_format(today(), "dd MMMM yyyy", locale="ru")stri_datetime_format(today(), "LLLL d, yyyy", locale="ru")

## [1] "ордибехешт"## [1] "ваисакха"## [1] "ияр"## [1] "рамадан"## [1] "бармуда"## [1] "миазия"## [1] "29 апреля 2021"## [1] "апрель 29, 2021"

Собственное форматирование дат по осям графиков

Иногда возникает необходимость собственного форматирования меток осей. Ниже пример по созданию такой функции

# сгенерируем тестовые данныеmap_tbl <- tibble(  date = as_date(Sys.time() + rnorm(10^3, mean = 0, sd = 60 * 60 * 24 * 7))) %>%  mutate(store = stri_c(sample(c("A", "F", "Y", "Z"), n(), replace = TRUE),                        sample(101:105, n(), replace = TRUE))) %>%  mutate(store_fct = as.factor(store)) %>%  mutate(fail_ratio = abs(rnorm(n(), mean = 0.3, sd = 1)))

my_date_format <- function (format = "dd MMMM yyyy", tz = "Europe/Moscow") {  scales:::force_all(format, tz)  # stri_datetime_fstr("%d.%m%n%A")  # stri_datetime_fstr("%d.%m (%a)")  function(x) stri_datetime_format(x, format, locale = "ru", tz = tz)}# такой же график, но в развертке по горизонталиgp <- map_tbl %>%  ggplot(aes(x = date, y = store_fct, fill = fail_ratio)) +  geom_tile(color = "white", size = 0.1) +  # scale_fill_distiller(palette = "RdYlGn", name = "Fail Ratio", label = comma) +  # scale_fill_distiller(palette = "RdYlGn", name = "Fail Ratio", guide = guide_legend(keywidth = unit(4, "cm"))) +  scale_fill_distiller(palette = "RdYlGn", name = "Fail Ratio") +  scale_x_date(breaks = scales::date_breaks("1 week"), labels = my_date_format("dd'.'MM' ('ccc')'")) +  coord_equal() +  labs(x = NULL, y = NULL, title = "Средний % сбоев по дням") +  theme_minimal() +  theme(plot.title = element_text(hjust = 0)) +  theme(axis.ticks = element_blank()) +  theme(axis.text = element_text(size = 7)) +  theme(axis.text.x = element_text(angle = 90, vjust = 0.5)) +  theme(legend.position = "bottom") +  theme(legend.key.width = unit(3, "cm"))gp

Тесты производительности

Простая математика

Создадим тестовый набор записей

base_df <- tibble(  start = Sys.time() + rnorm(10^3, mean = 0, sd = 60 * 24 * 3)) %>%  mutate(finish = start + rnorm(n(), mean = 100, sd = 60)) %>%  mutate(user_id = sample(as.character(1000:1100), n(), replace = TRUE)) %>%  arrange(user_id, start)dt <- as.data.table(base_df, key = c("user_id", "start")) %>%  .[, c("start", "finish") := lapply(.SD, as.numeric),     .SDcols = c("start", "finish")]

df <- group_by(base_df, user_id)bench::mark(  dplyr_v1 = df %>% transmute(delta_t = as.numeric(difftime(finish, start, units = "secs"))) %>% ungroup(),  dplyr_v2 = ungroup(df) %>% transmute(delta_t = as.numeric(difftime(finish, start, units = "secs"))),  dplyr_v3 = dt %>% transmute(delta_t = finish - start),  dt_v1 = dt[, .(delta_t = finish - start), by = user_id],  dt_v2 = dt[, .(delta_t = finish - start)],  check = FALSE # all_equal работает более корректно)

## # A tibble: 5 x 6##   expression      min   median `itr/sec` mem_alloc `gc/sec`##   <bch:expr> <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl>## 1 dplyr_v1      4.3ms   4.86ms      200.   103.1KB    11.4 ## 2 dplyr_v2     2.17ms   2.46ms      380.    17.9KB     6.24## 3 dplyr_v3     1.67ms   1.77ms      527.    29.8KB     8.51## 4 dt_v1       410.4us  438.7us     2139.    90.8KB     8.35## 5 dt_v2       304.4us  335.3us     2785.   264.6KB     8.38

У меня данные хранятся в формате год/месяц/число. Мне не все нужны, а только суббота, как мне отфильтровать?

# https://stackoverflow.com/questions/16347731/how-to-change-the-locale-of-r# https://jangorecki.gitlab.io/data.cube/library/stringi/html/stringi-locale.htmldf <- as.Date("2020-01-01") %>%   seq.Date(to = . + months(4), by = "1 day") %>%  tibble(date = .) %>%  mutate(wday = lubridate::wday(date, week_start = 1),         wday_abb_rus = lubridate::wday(date, label = TRUE, week_start = 1),         wday_abb_enu = lubridate::wday(date, label = TRUE, week_start = 1, locale = "English"),         wday_stri = stringi::stri_datetime_format(date, "EEEE", locale = "en"))# оставим только субботыfilter(df, wday == 6)

Предыдущая публикация R vs Python в продуктивном контуре.

В их внешнем облике ничто не вызывает подозрений. Более того, они даже кажутся тебе хорошо и давно знакомыми. Но это только до тех пор, пока ты их не проверишь. Вот тут-то они и проявят свою коварную сущность, сработав совсем не так, как ты ожидал. А иногда выкидывают такое, от чего волосы просто встают дыбом к примеру, теряют доверенные им секретные данные. Когда ты делаешь им очную ставку, они утверждают, что не знают друг друга, хотя в тени усердно трудятся под одним колпаком. Пора уже наконец-то вывести их на чистую воду. Давайте же и мы разберемся с этими подозрительными типами.

Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике. Сказать по правде, я составил эту статью в том числе и в качестве некоего справочника для самого себя, справочника, к которому можно было бы легко обратиться в спорных случаях. Поэтому он будет пополняться по мере обнаружения новых сюрпризов от подозрительных типов. Итак, в путь, о неутомимые следопыты баз данных!

Досье номер один. real/double precision/numeric/money

Казалось бы, числовые типы наименее проблемные с точки зрения сюрпризов в поведении. Но как бы не так. Поэтому с них и начнем. Итак...

Разучились считать

SELECT 0.1::real = 0.1?column?boolean---------f

В чем дело? В том, что PostgreSQL приводит нетипизированную константу 0.1 к типу double precision и пытается сравнить ее с 0.1 типа real. А это абсолютно разные значения! Суть в представлении вещественных чисел в машинной памяти. Поскольку 0.1 невозможно представить в виде конечной двоичной дроби (это будет 0.0(0011) в двоичном виде), числа с разной разрядностью будут отличаться, отсюда и результат, что они не равны. Вообще говоря, это тема для отдельной статьи, подробнее писать тут не буду.

Откуда ошибка?

SELECT double precision(1)ERROR:  syntax error at or near "("LINE 1: SELECT double precision(1)                               ^********** Ошибка **********ERROR: syntax error at or near "("SQL-состояние: 42601Символ: 24

Многие знают, что PostgreSQL допускает функциональную запись приведения типов. То есть можно написать не только 1::int, но и int(1), что будет равнозначно. Но только не для типов, название которых состоит из нескольких слов! Поэтому, если вы хотите привести числовое значение к типу double precision в функциональном виде, используйте алиас этого типа float8, то есть SELECT float8(1).

Что больше бесконечности?

SELECT 'Infinity'::double precision < 'NaN'::double precision?column?boolean---------t

Вон оно как! Оказывается, есть нечто, большее бесконечности, и это NaN! При этом документация PostgreSQL честными глазами смотрит на нас и утверждает, что NaN заведомо больше любого другого числа, а, следовательно, бесконечности. Справедливо и обратное для -NaN. Привет, любители матанализа! Но надо помнить, что все это действует в контексте вещественных чисел.

Округление глаз

SELECT round('2.5'::double precision)     , round('2.5'::numeric)      round      |  rounddouble precision | numeric-----------------+---------2                | 3

Еще один неожиданный привет от базы. И снова надо запомнить, что для типов double precision и numeric действуют разные округления. Для numeric обычное, когда 0,5 округляется в большую сторону, а для double precision округление 0,5 происходит в сторону ближайшего четного целого.

Деньги это нечто особое

SELECT '10'::money::float8ERROR:  cannot cast type money to double precisionLINE 1: SELECT '10'::money::float8                          ^********** Ошибка **********ERROR: cannot cast type money to double precisionSQL-состояние: 42846Символ: 19

По мнению PostgreSQL, деньги не являются вещественным числом. По мнению некоторых индивидуумов, тоже. Нам же надо помнить, что приведение типа money возможно только к типу numeric, равно как и к типу money можно привести только тип numeric. А вот с ним уже можно играться, как душе будет угодно. Но это будут уже не те деньги.

Smallint и генерация последовательностей

SELECT *  FROM generate_series(1::smallint, 5::smallint, 1::smallint)ERROR:  function generate_series(smallint, smallint, smallint) is not uniqueLINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...               ^HINT:  Could not choose a best candidate function. You might need to add explicit type casts.********** Ошибка **********ERROR: function generate_series(smallint, smallint, smallint) is not uniqueSQL-состояние: 42725Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.Символ: 18

Не любит PostgreSQL мелочиться. Какие такие последовательности на основании smallint? int, не меньше! Поэтому при попытке выполнения вышеприведенного запроса база пытается привести smallint к какому-то другому целочисленному типу, и видит, что таких приведений может быть несколько. Какое приведение выбрать? Это она решить не может, и поэтому падает с ошибкой.

Досье номер два. char/char/varchar/text

Ряд странностей присутствует и у символьных типов. Давайте тоже познакомимся с ними.

Это что за фокусы?

SELECT 'ПЕТЯ'::"char"     , 'ПЕТЯ'::"char"::bytea     , 'ПЕТЯ'::char     , 'ПЕТЯ'::char::bytea char  | bytea |    bpchar    | bytea"char" | bytea | character(1) | bytea-------+-------+--------------+--------      | \xd0  | П            | \xd09f

Что это за тип char, что это за клоун? Нам таких не надо Потому, что он прикидывается обычным char, даром что в кавычках. А отличается он от обычного char, который без кавычек, тем, что выводит только первый байт строкового представления, тогда как нормальный char выводит первый символ. В нашем случае первый символ буква П, которая в unicode-представлении занимает 2 байта, о чем свидетельствует конвертация результата в тип bytea. А тип char берет только первый байт этого unicode-представления. Тогда зачем этот тип нужен? Документация PostgreSQL говорит, что это специальный тип, используемый для особых нужд. Так что он вряд ли нам потребуется. Но посмотрите ему в глаза и не ошибитесь, когда встретите его с его особенным поведением.

Лишние пробелы. С глаз долой, из сердца вон

SELECT 'abc   '::char(6)::bytea     , 'abc   '::char(6)::varchar(6)::bytea     , 'abc   '::varchar(6)::bytea     bytea     |   bytea  |     bytea     bytea     |   bytea  |     bytea---------------+----------+----------------\x616263202020 | \x616263 | \x616263202020

Взгляните на приведенный пример. Я специально все результаты привел к типу bytea, чтобы было наглядно видно, что там лежит. Где хвостовые пробелы после приведения к типу varchar(6)? Документация лаконично утверждает: При приведении значения character к другому символьному типу дополняющие пробелы отбрасываются. Эту нелюбовь надо запомнить. И заметьте, что если строковая константа в кавычках сразу приводится к типу varchar(6), концевые пробелы сохраняются. Такие вот чудеса.

Досье номер три. json/jsonb

JSON отдельная структура, которая живет своей жизнью. Поэтому ее сущности и сущности PostgreSQL немного отличаются. Вот примеры.

Джонсон и Джонсон. Почувствуйте разницу

SELECT 'null'::jsonb IS NULL?column?boolean---------f

Все дело в том, что у JSON есть своя сущность null, которая не является аналогом NULL в PostgreSQL. В то же время, сам JSON-объект вполне может иметь значение NULL, поэтому выражение SELECT null::jsonb IS NULL (обратите внимание на отсутствие одинарных кавычек) на сей раз вернет true.

Одна буква меняет все

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json                     json                     json------------------------------------------------{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}---SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb             jsonb             jsonb--------------------------------{"1": [7, 8, 9], "2": [4, 5, 6]}

Все дело в том, что json и jsonb совершенно разные структуры. В json объект хранится как есть, а в jsonb он хранится уже в виде разобранной проиндексированной структуры. Именно поэтому во втором случае значение объекта по ключу 1 было заменено с [1, 2, 3] на [7, 8, 9], которое пришло в структуру в самом конце с тем же ключом.

С лица воды не пить

SELECT '{"reading": 1.230e-5}'::jsonb     , '{"reading": 1.230e-5}'::json          jsonb         |         json          jsonb         |         json------------------------+----------------------{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL в реализации JSONB меняет форматирование вещественных чисел, приводя их к классическому виду. Для типа JSON такого не происходит. Странно немного, но его право.

Досье номер четыре. date/time/timestamp

С типами даты/времени тоже есть некоторые странности. Посмотрим на них. Сразу оговорюсь, что некоторые из особенностей поведения становятся понятными, если хорошо понимать суть работы с часовыми поясами. Но это также тема для отдельной статьи.

Моя твоя не понимать

SELECT '08-Jan-99'::dateERROR:  date/time field value out of range: "08-Jan-99"LINE 1: SELECT '08-Jan-99'::date               ^HINT:  Perhaps you need a different "datestyle" setting.********** Ошибка **********ERROR: date/time field value out of range: "08-Jan-99"SQL-состояние: 22008Подсказка: Perhaps you need a different "datestyle" setting.Символ: 8

Казалось бы, что тут непонятного? Но все же база не понимает, что мы тут поставили на первое место год или день? И решает, что это 99 января 2008 года, что взрывает ей мозг. Вообще говоря, в случае передачи дат в текстовом формате нужно очень внимательно проверять то, насколько правильно база их распознала (в частности, анализировать параметр datestyle командой SHOW datestyle), поскольку неоднозначности в этом вопросе могут стоить очень дорого.

Ты откуда такой взялся?

SELECT '04:05 Europe/Moscow'::timeERROR:  invalid input syntax for type time: "04:05 Europe/Moscow"LINE 1: SELECT '04:05 Europe/Moscow'::time               ^********** Ошибка **********ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"SQL-состояние: 22007Символ: 8

Почему база не может понять явно указанное время? Потому что для часового пояса указана не аббревиатура, а полное наименование, которое имеет смысл только в контексте даты, поскольку учитывает историю изменения часовых поясов, а она без даты не работает. Да и сама формулировка строки времени вызывает вопросы а что же на самом деле имел в виду программист? Поэтому тут все логично, если разобраться.

Что ему не так?

Представьте себе ситуацию. У вас в таблице есть поле с типом timestamptz. Вы хотите его проиндексировать. Но понимаете, что строить по этому полю индекс не всегда оправдано ввиду его высокой селективности (почти все значения этого типа будут уникальными). Поэтому вы решаете снизить селективность индекса, приведя этот тип к дате. И получаете сюрприз:

CREATE INDEX "iIdent-DateLastUpdate"  ON public."Ident" USING btree  (("DTLastUpdate"::date));ERROR:  functions in index expression must be marked IMMUTABLE********** Ошибка **********ERROR: functions in index expression must be marked IMMUTABLESQL-состояние: 42P17

В чем дело? В том, что для приведения типа timestamptz к типу date используется значение системного параметра TimeZone, что делает функцию приведения типа зависимой от настраиваемого параметра, т.е. изменчивой (volatile). Такие функции в индексе недопустимы. В этом случае надо явно указывать, в каком часовом поясе производится приведение типа.

Когда now совсем даже не now

Мы привыкли, что now() возвращает текущую дату/время с учетом часового пояса. Но посмотрите на следующие запросы:

START TRANSACTION;SELECT now();            now  timestamp with time zone-----------------------------2019-11-26 13:13:04.271419+03...SELECT now();            now  timestamp with time zone-----------------------------2019-11-26 13:13:04.271419+03...SELECT now();            now  timestamp with time zone-----------------------------2019-11-26 13:13:04.271419+03COMMIT;

Дата/время возвращаются одинаковыми независимо от того, сколько времени прошло с момента предыдущего запроса! В чем дело? В том, что now() это не текущее время, а время начала текущей транзакции. Поэтому в рамках транзакции оно не меняется. Любой запрос, запускаемый вне рамок транзакции, оборачивается в транзакцию неявно, поэтому мы и не замечаем, что время, выдаваемое простым запросом SELECT now(); на самом деле-то не текущее Если хотите получить честное текущее время, нужно пользоваться функцией clock_timestamp().

Досье номер пять. bit

Strange a little bit

SELECT '111'::bit(4) bitbit(4)------1110

С какой стороны следует добавлять биты в случае расширения типа? Кажется, что слева. Но только у базы на этот счет другое мнение. Будьте осторожны: при несоответствии количества разрядов при приведении типа вы получите совсем не то, что хотели. Это относится как к добавлению битов справа, так и к урезанию битов. Тоже справа...

Досье номер шесть. Массивы

Даже NULL не стрельнул

SELECT ARRAY[1, 2] || NULL?column?integer[]---------{1,2}

Как нормальные люди, воспитанные на SQL, мы ожидаем, что результатом этого выражения будет NULL. Но не тут-то было. Возвращается массив. Почему? Потому что в данном случае база приводит NULL к целочисленному массиву и неявно вызывает функцию array_cat. Но все равно остается неясным, почему этот массивовый котик не обнуляет массив. Такое поведение тоже надо просто запомнить.

Подведем итог. Странностей хватает. Конечно, большинство из них, конечно, не настолько критичны, чтобы говорить о вопиюще неадекватном поведении. А другие объясняются удобством использования или частотой их применимости в тех или иных ситуациях. Но в то же время неожиданностей много. Поэтому надо о них знать. Если найдете еще что-то странное или необычное в поведении каких-либо типов, пишите в комментариях, с удовольствием дополню имеющиеся на них досье.