Только для читателей Lifeexample возможно открыть интернет-магазин на Moguta.CMS со скидкой в 15%
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 все это рассчитывал:) (от невежества-с:)
А как отнять год от текущей даты? Чтобы вместо 18.02.2014 в БД записывалось 18.02.2013?
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.
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
-> '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)
или можно проще?
Полезная статья, спасибо, то что нужно!
Только в «Получить выборку с этого понедельника по текущий день недели в SQL» и «Получить выборку с первого числа текущего месяца по текущий день недели в SQL» одинаковый код написан.
🙂