Только для читателей Lifeexample возможно открыть интернет-магазин на Moguta.CMS со скидкой в 15%

<<< PHP штрих код || Работа с XML в PHP >>>

SQL Работа с датами

25.06.2013
SQL Работа с датами

Здравствуйте, уважаемые читатели блога LifeExample, все меньше у меня остается времени на подготовку основательных материалов, но пока готовится более весомая статься предлагаю ознакомиться со справочной и очень, порой полезной, информацией, собранной мной с разных уголков рунета. Статья покажет, какая чаще всего востребована в SQL работа с датами.

“Время — ткань, из которой состоит жизнь” сказал Бенджамин Франклин. Интерпретируя данное высказывание в сферу программирования, получим “Время – то, что делает наши приложения живыми“. Работа со временем и датой, открывает новые возможности для простых скриптов.

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

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

Как получить текущую дату в SQL

1 вариант:

1
WHERE date = CURDATE()

2 вариант:

1
WHERE date = STR_TO_DATE(now(), '%Y-%m-%d')

Прибавить к дате один час в SQL

1
DATE_ADD('2013-03-30', INTERVAL 1 HOUR)

Прибавить к дате один день в SQL

1
DATE_ADD('2013-03-30', INTERVAL 1 DAY)

Аналогично можно прибавлять любое количество дней к текущей дате.

Прибавить к дате один месяц в SQL

1
DATE_ADD('2013-03-30', INTERVAL 1 MONTH)

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

Получить вчерашний день в SQL

Первый вариант:

1
DATE_ADD(CURDATE(), INTERVAL -1 DAY)

Второй вариант:

1
DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Получить дату начала текущей недели в SQL

Вот эта одна из самых сложных на первый взгляд задач, но решается очень просто:

1
CURDATE()-WEEKDAY(CURDATE());

Получить выборку с этого понедельника по текущий день недели в SQL

1
2
3
4
5
6
WHERE (
date BETWEEN
(CURDATE()-WEEKDAY(CURDATE()))
AND
CURDATE()
)

Получить выборку с первого числа текущего месяца по текущий день недели в SQL

1
2
3
4
5
WHERE (
date BETWEEN
(CURDATE()-WEEKDAY(CURDATE()))
 AND
CURDATE())

Как получить дату рождения пользователя в SQL

1
2
3
4
5
SELECT name, birth, CURRENT_DATE,
     (YEAR(CURRENT_DATE)-YEAR(birth))
     - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))
     AS age
FROM user;

Найти всех пользователей у которых день рождение в следующем месяце в SQL

1
2
SELECT name, birth FROM user
WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Или второй вариант:

1
2
SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

Кроме вышеприведенных кейсов по работе с датами в SQL, рекомендую ознакомиться с документацией по следующим операторам :

  • NOW() — Возвращает текущую дату и время.
  • CURDATE() — Возвращает текущую дату.
  • CURTIME() — Возвращаем текущее время.
  • DATE() — Состоит из двух частей даты и времени.
  • EXTRACT() — Возвращает одно значения даты/времени.
  • DATE_ADD() — Добавляет до выборки указанное число дней/мину/часов и т.д.
  • DATE_SUB() — Вычитываем указанный интервал от даты.
  • DATEDIFF() — Возвращает значение времени между двумя датами.
  • DATE_FORMAT() — Функция для различного вывода информации о времени.

Работа с датами в SQl, как оказывается не такая сложная, и теперь вместо того чтобы вычислять периоды средствами PHP можно делать это еще на этапе выполнения SQL запроса и получать необходимую выборку данных.

Чтобы не пропустить публикацию следующей статьи подписывайтесь на рассылку по E-mail или RSS ленту блога.

Нравится

Комментарии

  • Василий

    Спасибо за статью. Очень полезные возможности MySQL открыл для себя. А раньше как раз таки средствами PHP все это рассчитывал:) (от невежества-с:)

  • Elena

    А как отнять год от текущей даты? Чтобы вместо 18.02.2014 в БД записывалось 18.02.2013?

    • Andrey

      INTERVAL expr unit is permitted on either side of the + operator if the expression on the other side is a date or datetime value. For the — operator, INTERVAL expr unit is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      mysql> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
              -> '2009-01-01 00:00:00'
      mysql> SELECT INTERVAL 1 DAY + '2008-12-31';
              -> '2009-01-01'
      mysql> SELECT '2005-01-01' - INTERVAL 1 SECOND;
              -> '2004-12-31 23:59:59'
      mysql> SELECT DATE_ADD('2000-12-31 23:59:59',
          ->                 INTERVAL 1 SECOND);
              -> '2001-01-01 00:00:00'
      mysql> SELECT DATE_ADD('2010-12-31 23:59:59',
          ->                 INTERVAL 1 DAY);
              -> '2011-01-01 23:59:59'
      mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
          ->                 INTERVAL '1:1' MINUTE_SECOND);
              -> '2101-01-01 00:01:00'
      mysql> SELECT DATE_SUB('2005-01-01 00:00:00',
          ->                 INTERVAL '1 1:1:1' DAY_SECOND);
              -> '2004-12-30 22:58:59'
      mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
          ->                 INTERVAL '-1 10' DAY_HOUR);
              -> '1899-12-30 14:00:00'
      mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
              -> '1997-12-02'
      mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
          ->            INTERVAL '1.999999' SECOND_MICROSECOND);
              -> '1993-01-01 00:00:01.000001'
  • Александр

    Спасибо… то , что нужно . По делу и доступно для понимания ..

  • Макс

    Спасибо!
    Все дни текущей недели:

    WHERE (date BETWEEN (CURDATE()-WEEKDAY(CURDATE())) AND (CURDATE()-WEEKDAY(CURDATE()))+6)

    или можно проще?

  • Viktor

    Полезная статья, спасибо, то что нужно!
    Только в «Получить выборку с этого понедельника по текущий день недели в SQL» и «Получить выборку с первого числа текущего месяца по текущий день недели в SQL» одинаковый код написан.
    🙂

  • Оставить комментарий

    Не отвечать

    Подписаться на комментарии к этой статье по RSS

    Яндекс.Метрика