Файл: Практическая работа 1 sql создание базы данных и таблиц Цель.docx

TIMESTAMPDIFF(interval, date1, date2) функция вычисляет разницу между датами date2 и date1 в единицах, указанных в параметре interval. Возможные значения параметра interval:

FRAC_SECOND - микросекунды
SECOND - секунды
MINUTE - минуты
HOUR - часы
DAY - дни
WEEK - недели
MONTH - месяцы
QUARTER - кварталы
YEAR - годы

• 
  
  
• 
  SELECT TIMESTAMPDIFF(DAY, '2011-04-02', '2011-04-17') AS days,
  
• 
  TIMESTAMPDIFF(HOUR, '2011-04-16 20:14:00', '2011-04-17 23:58:20') AS houres;
  

• 
  SUBTIME(date, time) функция вычитает из времени date время time:
  
• 
  
  
• 
  SELECT SUBTIME('2011-04-18 23:17:00', '02:15:30');
  

DATE(datetime) возвращает дату, отсекая время. Например:

• 
  
  
• 
  SELECT DATE('2011-04-15 00:03:20');
  

TIME(datetime) возвращает время, отсекая дату. Например:

• 
  
  
• 
  SELECT TIME('2011-04-15 00:03:20');
  

TIMESTAMP(date) функция принимает дату date и возвращает полный вариант со временем. Например:

• 
  
  
• 
  SELECT TIMESTAMP('2011-04-17');
  

DAY(date) и DAYOFMONTH(date) функции-синонимы, возвращают из даты порядковый номер дня месяца:

• 
  
  
• 
  SELECT DAY('2011-04-17'), DAYOFMONTH('2011-04-17');
  

DAYNAME(date), DAYOFWEEK(date) и WEEKDAY(date) функции возвращают день недели, первая - его название, вторая - номер дня недели (отсчет от 1 - воскресенье до 7 - суббота), третья - номер дня недели (отсчет от 0 - понедельник, до 6 - воскресенье:

• 
  
  
• 
  SELECT DAYNAME('2011-04-17'), DAYOFWEEK('2011-04-17'), WEEKDAY('2011-04-17');
  

WEEK(date), WEEKOFYEAR(datetime) обе функции возвращают номер недели в году, первая для типа date, а вторая - для типа datetime, у первой неделя начинается с воскресенья, у второй - с понедельника:

• 
  
  
• 
  SELECT WEEK('2011-04-17'), WEEKOFYEAR('2011-04-17 23:40:00');
  

---

• 
  MONTH(date) и MONTHNAME(date) обе функции возвращают значения месяца. Первая - его числовое значение (от 1 до 12), вторая - название месяца:
  
• 
  
  
• 
  SELECT MONTH('2011-04-17'), MONTHNAME('2011-04-17');
  

• 
  QUARTER(date) функция возвращает значение квартала года (от 1 до 4):
  
• 
  
  
• 
  SELECT QUARTER('2011-04-17');
  

• 
  YEAR(date) функция возвращает значение года (от 1000 до 9999):
  
• 
  
  
• 
  SELECT YEAR('2011-04-17');
  

• 
  DAYOFYEAR(date) возвращает порядковый номер дня в году (от 1 до 366):
  
• 
  
  
• 
  SELECT DAYOFYEAR('2011-04-17');
  

• 
  HOUR(datetime) возвращает значение часа для времени (от 0 до 23):
  
• 
  
  
• 
  SELECT HOUR('2011-04-17 18:20:03');
  

• 
  MINUTE(datetime) возвращает значение минут для времени (от 0 до 59):
  
• 
  
  
• 
  SELECT MINUTE('2011-04-17 18:20:03');
  

• 
  SECOND(datetime) возвращает значение секунд для времени (от 0 до 59):
  
• 
  
  
• 
  SELECT SECOND('2011-04-17 18:20:03');
  

• 
  EXTRACT(type FROM date) возвращает часть date определяемую параметром type:
  
• 
  
  
• 
  SELECT EXTRACT(YEAR FROM '2011-04-17 23:15:18') AS year,
  
• 
  EXTRACT(MONTH FROM '2011-04-17 23:15:18') AS mon,
  
• 
  EXTRACT(DAY FROM '2011-04-17 23:15:18') AS day,
  
• 
  EXTRACT(HOUR FROM '2011-04-17 23:15:18') AS hour,
  
• 
  EXTRACT(MINUTE FROM '2011-04-17 23:15:18') AS min,
  
• 
  EXTRACT(SECOND FROM '2011-04-17 23:15:18') AS sec;
  

• 
  TO_DAYS(date) и FROM_DAYS(n) взаимообратные функции. Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату:
  
• 
  
  
• 
  SELECT TO_DAYS('2011-04-17'), FROM_DAYS(734420);
  

• 
  UNIX_TIMESTAMP(date) и FROM_UNIXTIME(n) взаимообратные функции. Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату:
  
• 
  
  
• 
  SELECT UNIX_TIMESTAMP('2011-04-17'), FROM_UNIXTIME(1302524000);
  

---

• 
  TIME_TO_SEC(time) и SEC_TO_TIME(n) взаимообратные функции. Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время:
  
• 
  
  
• 
  SELECT TIME_TO_SEC('22:10:30'), SEC_TO_TIME(45368);
  

• 
  MAKEDATE(year, n) функция принимает год и номер дня в году и преобразует их в дату:
  
• 
  
  
• 
  SELECT MAKEDATE(2011, 120);
  

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

Практическая работа № 14

---

MYSQL функции форматирования даты и времени

Цель: научиться работать с функцией форматирования даты и времени

Эти функции также предназначены для работы с календарными типами данных. Рассмотрим их подробнее.

• 
  DATE_FORMAT(date, format) форматирует дату date в соответствии с выбранным форматом formate. Эта функция очень часто используется. Например, в MySQL дата имеет формат представления YYYY-MM-DD (год-месяц-число), а нам привычнее формат DD-MM-YYYY (число-месяц-год). Поэтому для привычного нам отображения даты ее необходимо переформатировать. Давайте сначала приведем запрос, а затем разберемся, как задавать формат:
  
• 
  
  
• 
  SELECT DATE_FORMAT(CURDATE(), '%d.%m.%Y');
  

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

Опред	Описание
%a	Сокращенное наименование дня недели (Mon - понедельник, Tue - вторник, Wed - среда, Thu - четверг, Fri - пятница, Sat - суббота, Sun - воскресенье). Пример: SELECT DATE_FORMAT(CURDATE(), '%a'); Результат:
%b	Сокращенное наименование месяцев (Jan - январь, Feb - февраль, Mar - март, Apr - апрель, May - май, Jun - июнь, Jul - июль, Aug - август, Sep - сентябрь, Oct - октябрь, Nov - ноябрь, Dec - декабрь). Пример: SELECT DATE_FORMAT(CURDATE(), '%b'); Результат:
%c	Месяц в числовой форме (1 - 12). Пример: SELECT DATE_FORMAT(CURDATE(), '%с'); Результат:
%d	День месяца в числовой форме с нулем (01 - 31). Пример: SELECT DATE_FORMAT(CURDATE(), '%d'); Результат:
%D	День месяца в английском варианте (1st, 2nd...). Пример: SELECT DATE_FORMAT(CURDATE(), '%D'); Результат:
%e	День месяца в числовой форме без нуля (1 - 31). Пример: SELECT DATE_FORMAT(CURDATE(), '%e'); Результат:
%H	Часы с ведущим нулем от 00 до 23. Пример: SELECT DATE_FORMAT('2011-04-15 23:03:20', '%H'); Результат:
%h	Часы с ведущим нулем от 00 до 12. Пример: SELECT DATE_FORMAT('2011-04-15 23:03:20', '%h'); Результат:
%i	Минуты от 00 до 59. Пример: SELECT DATE_FORMAT('2011-04-15 23:03:20', '%i'); Результат:
%j	День года от 001 до 366. Пример: SELECT DATE_FORMAT('2011-04-15 23:03:20', '%j'); Результат:
%k	Часы c ведущим нулем от 0 до 23. Пример: SELECT DATE_FORMAT('2011-12-31 01:03:20', '%k'); Результат:
%l	Часы без ведущим нуля от 1 до 12. Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%l'); Результат:
%M	Название месяца без сокращения. Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%M'); Результат:
%M	Название месяца без сокращения. Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%M'); Результат:
%m	Месяц в числовой форме с ведущим нулем (01 - 12). Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%m'); Результат:
%p	АМ или РМ для 12-часового формата. Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%p'); Результат:
%r	Время в 12-часовом формате. Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%r'); Результат:
%s	Секунды от 00 до 59. Пример: SELECT DATE_FORMAT('2011-04-15 00:03:20', '%s'); Результат:
%T	Время в 24-часовом формате. Пример: SELECT DATE_FORMAT('2011-04-15 21:03:20', '%T'); Результат:
%u	Неделя (00 - 52), где первым днем недели считается понедельник. Пример: SELECT DATE_FORMAT('2011-04-17 21:03:20', '%u'); Результат:
%U	Неделя (00 - 52), где первым днем недели считается воскресенье. Пример: SELECT DATE_FORMAT('2011-04-17 21:03:20', '%U'); Результат:
%W	Название дня недели без сокращения. Пример: SELECT DATE_FORMAT('2011-04-17 21:03:20', '%W'); Результат:
%w	Номер дня недели (0 - воскресенье, 6 - суббота). Пример: SELECT DATE_FORMAT('2011-04-17 21:03:20', '%w'); Результат:
%Y	Год, 4 разряда. Пример: SELECT DATE_FORMAT('2011-04-17 21:03:20', '%Y'); Результат:
%y	Год, 2 разряда. Пример: SELECT DATE_FORMAT('2011-04-17 21:03:20', '%y'); Результат:

---

• 
  STR_TO_DATE(date, format) функция обратная предыдущей, она принимает дату date в формате format, а возвращает дату в формате MySQL.
  
• 
  
  
• 
  SELECT STR_TO_DATE('17.04.2011 23:50', '%d.%m.%Y %H:%i');
  

 Как видите, сама функция GET_FORMAT() возвращает формат представления, а вместе с функцией DATE_FORMAT() выдает дату в нужном формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.

• 
  TIME_FORMAT(time, format) функция аналогична функции DATE_FORMAT(), но используется только для времени:
  
• 
  
  
• 
  SELECT TIME_FORMAT('22:38:15', '%H-%i-%s');
  

• 
  GET_FORMAT(date, format) функция возвращает строку форматирования, соответствующую одному из пяти форматов времени: 
  
  EUR - европейский стандарт
  USA - американский стандарт
  JIS - японский индустриальный стандарт
  ISO - стандарт ISO (международная организация стандартов)
  INTERNAL - интернациональный стандарт
  
  Эту функцию хорошо использовать совместно с предыдущей - DATE_FORMAT(). Посмотрим на примере:
  
• 
  
  
• 
  SELECT GET_FORMAT(DATE, 'EUR'), DATE_FORMAT('2011-04-17', GET_FORMAT(DATE, 'EUR'));
  

 Как видите, сама функция GET_FORMAT() возвращает формат представления, а вместе с функцией DATE_FORMAT() выдает дату в нужном формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.
Ну вот, теперь вы знаете о работе с датами и временем в MySQL практически все. Это вам очень пригодится при разработке различных web-приложений. Например, если пользователь в форму на сайте вводит дату в привычном ему формате, вам не составит труда применить нужную функцию, чтобы в БД дата попала в нужном формате. 

Практическая работа № 15

---