Преобразование дат при помощи SQL

Задача
Вы хотите преобразовать даты, используя предложения SQL.

Решение
При экспорте используйте функцию DATE_FORMAT() для преобразования значений. При импорте считайте значения в строковый столбец и преобразуйте в настоящие значения DATE.

Обсуждение
Предположим, что вы хотите экспортировать данные из MySQL в приложение, которое не понимает даты в формате ISO. Можно экспортировать данные в файл, оставляя даты в формате ISO, затем пропустить этот файл через утилиту типа cvt_date.pl для преобразования значений в нужный формат даты.

Есть и другой способ – экспортировать даты непосредственно в требуемый формат, преобразуя их посредством DATE_FORMAT(). Предположим, что вам нужно экспортировать данные из таблицы, при этом даты должны быть в формате США (MM-DD-CCYY). Ниже приведен сценарий, выполняющий такую операцию. Он принимает в качестве аргументов имена базы данных и таблицы, затем выгружает таблицу в формате значений, разделенных символами табуляции, с переформатированием столбцов DATE, DATETIME и TIMESTAMP. Сценарий исследует метаданные таблицы для получения типов столбцов, затем формирует предложение SELECT, использующее функцию DATE_FORMAT() для форматирования дат.


Другие столбцы таблицы записываются без изменения:

#! /usr/bin/perl -w
# iso_to_us.pl – Экспорт таблицы с датами, преобразованными из формата ISO# (CCYY-MM-DD) в формат США (MM-DD-CCYY). Для этого формируется предложение SELECT,
# выбирающее все столбцы таблицы и использующее DATE_FORMAT() для перезаписи дат.
# Записывает каждую строку как значения, разделенные символами табуляции,
# признак конца строки – символ перевода строки.
use strict;
use DBI;
# ...обработка опций командной строки (не приводится) ...
@ARGV == 2 or die "Usage: $0 [options] db_name tbl_name\n";
my $db_name = shift (@ARGV);
my $tbl_name = shift (@ARGV);
# ... соединение с базой данных (не приводится) ...
# Читать из MySQL метаданные таблицы для получения имен и типов столбцов.
# Информация о типах используется для выявления столбцов DATE, DATETIME и TIMESTAMP
# и перезаписи их содержимого с помощью DATE_FORMAT().
my @col;
my $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
$sth->execute ();
while (my @row = $sth->fetchrow_array ())
{
if ($row[1] =~ /^datetime|timestamp/)
{
$row[0] = "DATE_FORMAT($row[0], '%m-%d-%Y %T') AS $row[0]";
}
elsif ($row[1] =~ /^date/)
{
$row[0] = "DATE_FORMAT($row[0], '%m-%d-%Y') AS $row[0]";
}
push (@col, $row[0]);
}
my $query = "SELECT\n\t" .


join (",\n\t", @col) . "\nFROM $tbl_name";
# Выполнить предложение SELECT и выгрузить результат
$sth = $dbh->prepare ($query);
$sth->execute ();
while (my @val = $sth->fetchrow_array ())
{
# преобразовать значения NULL (undef) в пустые строки
@val = map { defined ($_) ? $_ : "" } @val;
print join ("\t", @val) . "\n";
}
$dbh->disconnect ();
exit (0);

Чтобы посмотреть, как работает сценарий, создадим такую таблицу:

CREATE TABLE datetbl
(i INT,
c CHAR(10),
d DATE,
dt DATETIME,
ts TIMESTAMP
);

Для экспорта содержимого datetbl сценарий формирует такое предложение SELECT:

SELECT
i,
c,
DATE_FORMAT(d, '%m-%d-%Y') AS d,
DATE_FORMAT(dt, '%m-%d-%Y %T') AS dt,
DATE_FORMAT(ts, '%m-%d-%Y %T') AS ts
FROM datetbl

То есть если datetbl содержит следующие строки:

3 abc 2001-12-31 2001-12-31 12:05:03 20011231120503
4 xyz 2002-01-31 2002-01-31 12:05:03 20020131120503

Сценарий генерирует такой вывод:

3 abc 12-31-2001 12-31-2001 12:05:03 12-31-2001 12:05:03
4 xyz 01-31-2002 01-31-2002 12:05:03 01-31-2002 12:05:03

При импортировании дат не-ISO в MySQL обычно сначала выполняется преобразование в формат ISO.


Иначе пришлось бы импортировать их как символьные строки, что делает невозможным их использование во временном контексте. Однако в некоторых случаях можно импортировать даты не-ISO как строки, а затем преобразовать их в значения DATE стандарта ISO.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Диагностическая утилита для LOAD DATA
Извлечение и перестановка столбцов файлов данных
Импорт XML в MySQL
Импорт с помощью LOAD DATA и утилиты mysqlimport
Импорт файлов в формате CSV

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных