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

В ISO (Международной организации по стандартам) даже есть модуль, посвященный тому, как следует писать дату и время.

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

В этом руководстве мы рассмотрим наиболее распространенные форматы даты и времени. Также будут подробные инструкции о том, как лучше всего справиться с ними в вашем проекте Data Analytics.

БЕСПОРЯДКА. Если вам нужно преобразовать любую дату/время, не стесняйтесь проверить наш инструмент www.cleanspreadsheets.com, который позволяет автоматически очищать любой формат даты в электронных таблицах.

Предположения

  1. Вы следуете стандартному григорианскому календарю
  2. Цифры арабские, например, 1, 3, 23.
  3. Ваш язык английский

Почему я должен заботиться об этом?

Есть несколько проблем, с которыми вы можете столкнуться, если все ваши даты не совпадают

  1. Напрасная трата ресурсов — допустим, вы хотите отправить электронное письмо всем клиентам, которые приобрели ваш продукт после определенной даты.

Если дата покупки записана как 4 мая 2012 года для одних клиентов и 27 апреля 2015 года для других, вы можете ошибочно получить неверный список. И тратьте деньги и время, отправляя электронные письма не тем людям. Или оставить деньги на столе.

2. Неправильный анализ. Неверные данные могут быстро испортить ваш аналитический проект. Это может быть что-то такое же простое, как сортировка списка клиентов по дате их покупки.

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

Как можно решить эти проблемы?

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

  • ГГГГ-ММ-ДДТЧЧ:ММ:ССZ (2018–01–24T13:02:45Z)
  • ГГГГ-ММ-ДДТЧЧ:ММ:СС+/ -TH:TM(2018–01–24T13:02:45–05:00 или 2018–01–24T17:09:23+05:30)

Z в конце первого формата означает, что время находится в часовом поясе UTC. UTC — это время по Гринвичу.

+/-TH:TM в конце второго формата означает разницу с часовым поясом UTC. Например, в Нью-Йорке -04:00, в Мумбаи +05:30.

Если вы просто работаете в пределах 1 часового пояса, мы рекомендуем следующий формат. Это не строгий стандарт ISO (с 2019 года), но все же предотвращает любые проблемы с грязными данными. Кроме того, он выглядит намного чище и менее запутанным без T.

  • ГГГГ-ММ-ДД ЧЧ:ММ:СС(2019–05– 14 13:02:45)

И без формата времени, если вы просто работаете с датами

  • ГГГГ-ММ-ДД (2019–05–14)

Давайте еще немного разберем этот формат.

  • ГГГГ — год, записанный в полном 4-летнем формате, например 2005. Технически ISO допускает все значения от 0000 (1BC) до 9999, но годы до 1583 и после/до 1BC/9999AD автоматически не распознаются. .
  • ММ — месяц, дополненный 0. Таким образом, январь — это 01. 01–12 — это диапазон.
  • ДД — день, дополненный 0. Таким образом, однозначные даты имеют префикс 0. Например, 02, 05. 01–31 — это диапазон, зависящий от месяца.
  • ЧЧ — час времени в 24-часовом формате, дополненный 0. Например, 04 для 4:00, 14 для 14:00 и т. д. 00–24 — это диапазон.
  • ММ — минуты времени, дополненные 0. Например, 06, 25, 59. 00–60 — это диапазон.
  • SS — секунды времени, дополненные 0. Тот же пример, что и выше. 00–60 — это диапазон. Если вам нужна большая точность, вы можете добавить соответствующие десятичные дроби. Например: 23:51 32 секунды и 30 миллисекунд будут 23:41:32,5.

Вы можете использовать другой разделитель для даты и времени, если хотите, но как для наглядности, так и для согласованности мы рекомендуем использовать дефис (-) для разделения даты и двоеточие (:) для разделения времени.

Давайте посмотрим, как форматировать несогласованные даты в этот стандарт.

Форматы даты

Мир пишет даты по-разному. Есть три основных положения дня, месяца и года, о которых нужно знать.

  1. День месяц год
  2. Год месяц день
  3. Месяц день год

Разделители

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

Это может быть знак препинания

  • 3–26–1991
  • 2015/4/17
  • 14.2.78

Или пространство

  • 08 14 88

Или комбинация знаков препинания и пробела

  • 8 января 2019 г.

Вы можете выполнить следующие шаги, чтобы получить различные компоненты. Это будет работать независимо от того, как отформатирована дата: день, месяц, год или год, месяц, день и т. д. НО вам нужно будет знать формат даты, чтобы в конечном итоге очистить каждый компонент.

Мы будем использовать эти 2 даты в качестве примера.

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

  1. Замените любой из разделительных знаков препинания: [-][.][/] пробелом.

2. Разделите дату, используя пробел в качестве разделителя

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

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

1998–01–05

2007–08–14

Вам нужно знать, каков порядок компонентов, прежде чем начать преобразование. Например, если это день, месяц, год или год, месяц, день и т. д.

День

Чтобы преобразовать дневной компонент

  • Удалите все, что привязано к номеру
  • Подтвердите, что это двузначное значение

Удалите все нечисловые символы, такие как nd со 2-го или th с 30-го

Добавьте 0 перед однозначными значениями, такими как 7 -> 07.

Месяц

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

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

Если месяц является числом, добавьте 0 к любым однозначным значениям, таким как 2 -> 02.

Если месяц написан, создайте карту слова (слов) с номером месяца, затем «Найти и заменить». Например:

январь, январь -› 01

февраль, февраль -› 02

Год

Год обычно представляет собой двухзначное или четырехзначное число. Пример: 1999 или 99. Однако может быть и пунктуация, например «99».

Для двузначного числа вам придется полагаться на свои бизнес-правила. Если, например, 99 — это 1999 или 1899 год.

Удалите любые нечисловые символы, такие как апостроф (‘) из ‘99.

Преобразуйте любой двузначный год в четырехзначный в зависимости от правил ваших данных.

Теперь мы можем переставить очищенные компоненты, чтобы создать даты ГГГГ-ММ-ДД, соединив их с помощью дефиса [-] разделителя.

1998–01–05

2007–08–14

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

Теперь давайте обсудим форматы времени. Двумя основными форматами являются 12-часовые часы с индикатором AM/PM или 24-часовые часы. Секунды также могут быть включены, но не являются обязательными. То же самое можно сказать о миллисекундах и дальнейших делениях.

  • Час:минута:секунды AM/PM (12 часов)
  • Часы:Минуты:Секунды (24 часа)

Как и дата, компоненты времени также отделены друг от друга. Наиболее распространены сепараторы.

  • Двоеточие [:], 18:45
  • Полные слова, 18 часов 23 минуты 52 секунды
  • Сокращения, 13х21м
  • Нет, 2137

Теперь мы увидим, как очистить время для стандартизации в стандарте ISO ЧЧ:ММ:СС. Мы будем использовать 6 следующих раз в качестве примеров.

В отличие от дат, время в основном следует за часами, минутами, секундами, поэтому вам не нужно знать формат.

  1. Удалите все возможные разделители: [:][часы][минуты][секунды][ч][м][с][чч][мм][сс][час][мин][сек][час][минута] [секунд]. И все пробелы

2. Проверьте, является ли ваша дата AM или PM. Если это так, удалите AM или PM и посчитайте, сколько цифр осталось.

Если это нечетное число, например 5, 7 и т. д., посмотрите на первую цифру (час).

  • Если это нечетное число и дата имеет AM, поставьте 0 в начале. Если дата имеет PM, добавьте 12 к первой цифре и замените первую цифру результатом.

3. Подсчитайте цифры всех ваших дат. Для любой даты с точкой/точкой [.] подсчитайте цифры до точки/точки [.]. Если число является нечетным, добавьте 0 в начале.

4. Теперь все время должно форматироваться. Мы можем разбить его на компоненты, отсчитав от начала на 2 секунды. После второго компонента (минуты) результатом значений будут секунды.

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

  • 18:23
  • 18:19
  • 14:53:27
  • 05:27
  • 04:09:33.7

6. [Необязательно], если вы работаете в разных часовых поясах, вам нужно знать, насколько далеко от UTC каждый раз. Затем вам нужно решить, хотите ли вы хранить информацию о часовом поясе или каждый раз конвертировать в UTC.

Если вы хотите преобразовать все время в UTC, получите разницу в формате UTC. Например, в Нью-Йорке -04:00 во время перехода на летнее время. Мумбаи +05:30.

Значение -04:00 и +05:30 называется смещением UTC.

Для Нью-Йорка это означает, что время отстает от UTC на 4 часа. Поэтому добавьте 4 часа к часовой составляющей ваших дат. Если это выходит за пределы 24, вычтите на 12.

18:23 в Нью-Йорке становится 22:23 UTC.

Если вы храните время в формате UTC, вы можете добавить Z или +00:00 в конце времени.

22:23Z or 22:23+00:00

Если вы храните в любом другом часовом поясе, вам нужно добавить его смещение UTC.

22:23–04:00

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

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

Наиболее распространенными разделителями даты и времени являются пробелы и стандарт ISO T.

  • 4/23/1998 4:45PM
  • 1998–04–23T16:45Z

Независимо от того, какой разделитель используется, используйте его, чтобы разделить значение на дату и время,очистить обе части по отдельности, а затем снова объединить их с помощью пробеларазделитель или разделитель T

  • 1998–04–23 16:45
  • 1998–04–23T16:45+00:00

Сохранение значений DateTime

Теперь, когда вы очистили значение DateTime, вам нужно где-то его сохранить.

Большинство современных баз данных, языков программирования и электронных таблиц имеют тип данных DateTime. Если вы собираетесь в основном использовать свои данные только в одной базе данных, языке программирования или программе для работы с электронными таблицами, вы должны хранить их как тип данных DateTime.

Однако, если вы собираетесь перемещать свои данные между несколькими базами данных, языками программирования или электронными таблицами, вам следует хранить их как текст/строку/символ.

Я знаю! Я знаю! Это кажется святотатством. Но, как показывает наш опыт, при перемещении данных все, скорее всего, сломается, когда задействованы типы данных DateTime. Каждая система обычно имеет различную реализацию типа данных DateTime.

Если вы привели дату в соответствие со стандартом ISO ГГГГ-ММ-ДДTЧЧ:ММ:СС, то сохранение их в виде текста не должно вызывать проблем. Вы можете легко сравнить и отсортировать эти даты.

Куда пойти отсюда?

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

Если вы знаете Python, то уже есть простой способ. Об этом мы писали здесь.

А если вам нужно очистить даты в электронных таблицах, вы можете воспользоваться нашим инструментом Очистить электронные таблицы, чтобы автоматически очистить и преобразовать любую дату в ваших электронных таблицах в формат ISO.

Удачной очистки данных!