Как пишется дата в sql

Работа с датами и временем

Для работы с датой и временем в MySQL есть несколько типов данных: DATE, TIME, DATETIME и TIMESTAMP.

Отличие TIMESTAMP и DATETIME

Типы данных DATETIME и TIMESTAMP в MySQL похожи друг на друга, так как оба направлены на хранение даты и времени. Но между ними есть ряд существенных отличий, определяющих какой из этих типов данных когда лучше использовать.

DATETIME

Хранит значения в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 и при этом занимает 8 байт. Этот тип данных не зависит от временной зоны, установленной в MySQL. Он всегда отображается ровно в таком виде, в котором был установлен и в котором храниться в базе данных. То есть при изменении часового пояса, отображение времени не измениться.

CREATE TABLE datetime_table (datetime_field DATETIME);
SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL
INSERT INTO datetime_table VALUES("2022-06-16 16:37:23");
SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL
SELECT * FROM datetime_table;

TIMESTAMP

Хранит сколько прошло секунд с 1970-01-01 00:00:00 по нулевому часовому поясу и занимает 4 байта. При выборках отображается с учетом текущего часового пояса. Часовой пояс можно задать в настройках операционной системы, где работает MySQL, в глобальных настройках MySQL или в конкретной сессии. В базе данных при создании записи с типом TIMESTAMP значение сохраняется по нулевому часовому поясу.

CREATE TABLE timestamp_table (timestamp_field TIMESTAMP);
SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL
INSERT INTO timestamp_table VALUES("2022-06-16 16:37:23");
SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL
SELECT * FROM timestamp_table;

Также стоит помнить о существом ограничении TIMESTAMP в диапазоне возможных значений от 1970-01-01 00:00:01 до 2038-01-19 03:14:07, что ограничивает его применении. Так, данный тип данных не подойдет для хранения дат рождения пользователей.

Способ задания значений

Значения DATETIME, DATE и TIMESTAMP могут быть заданы одним из следующих способов:

• Как строка в формате YYYY-MM-DD HH:MM:SS или в формате YY-MM-DD HH:MM:SS для указания даты и времени
• Как строка в формате YYYY-MM-DD или в формате YY-MM-DD для указания только даты

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

CREATE TABLE date_table (datetime TIMESTAMP);
INSERT INTO date_table VALUES("2022-06-16 16:37:23");
INSERT INTO date_table VALUES("22.05.31 8+15+04");
INSERT INTO date_table VALUES("2014/02/22 16*37*22");
INSERT INTO date_table VALUES("20220616163723");
INSERT INTO date_table VALUES("2021-02-12");
SELECT * FROM date_table;

Популярные функции для работы с датами

Литералы

Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:

• целочисленные — 0, -34, 45;
• вещественные — 0.0, -3.14, 3.23e-23;
• строковые — ‘текст’, n’текст’, ‘don»t!’;
• дата — DATE ‘2008-01-10’;
• время — TIME ’15:12:56′;
• временная отметка — TIMESTAMP ‘2008-02-14 13:32:02’;
• логический тип — true, false;
• пустое значение — null.

Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.

В MySQL для временных литерал строка должна быть заключена в скобки: DATE (‘2008-01-10’).

Формат даты по умолчанию обычно определяется настройкой БД. Продвинутые СУБД могут
автоматически определять некоторые форматы (DATE (‘2008.01.10’))
или как в Oracle имеют функцию преобразования (to_date(‘01.02.2003′,’dd.mm.yyyy’)).
Для упрощения во многих СУБД там, где подразумевается дата,
перед строкой необязательно ставить имя типа.

Интервал времени

Синтаксис и реализация интервалов отличается на разных СУБД.

Oracle

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

-- годовые интервалы
-- интервал в 99 лет
INTERVAL '99' YEAR

-- интервал в 999 лет в скобках
-- указана точность для годов
INTERVAL '999' YEAR(3)

-- интервал в 999 лет и 3 месяца в скобках
-- после TO указывается точность самого промежутка
-- месяцы указываются через -
INTERVAL '999-3' YEAR(3) TO MONTH

-- интервал в 99 лет и два месяца
-- это отрицательный интервал
INTERVAL '-99-2' YEAR TO MONTH

-- дневные интервалы
-- интервал в 200 дней
INTERVAL '200' DAY(3)
 
-- интервал в 200 дней и 6 часов
INTERVAL '200 6' DAY(3) TO HOUR

-- интервал в 200 дней, 6 часов
-- и 10 минут
INTERVAL '200 6:10' DAY(3) TO MINUTE

-- интервал в 200 дней, 6 часов,
-- 10 минут и 7 секунд
INTERVAL '200 6:10:7' DAY(3) TO SECOND

-- интервал в 200 дней, 6 часов,
-- 10 минут, 7 секунд и 333 милисекунды
INTERVAL '200 6:10:7.333' DAY(3) TO SECOND(3)

-- пример интервала в запросе
-- выборка интервала в два дня
select INTERVAL '2' day from dual;

PostgreSQL

интервалы указываются в виде строки, в которой перечисляются значение и тип промежутка:

• microsecond — микросекунды;
• millisecond — милисекунды;
• second — секунды;
• minute — минуты;
• hour — часы;
• day — дни;
• week — недели;
• month — месяцы;
• year — года;
• century — век;
• millennium — тысячелетие.

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

-- интервал в три года
INTERVAL '3 year'

-- интервал в три года и три дня
INTERVAL '3 years 3 day'

-- интервал в три года, три дня
-- и 3 минуты
INTERVAL '3 year 3 day 3 minute'

-- интервал в 3 дня, 7 часов,
-- 7 минут и 5 секунд
INTERVAL '3 7:07:05' 

-- пример интервала в запросе
-- выборка интервала в два дня
select INTERVAL '2 day'; 

MySQL

Только сложные интервалы, состоящие из более одного типа промежутков, указываются в строке.
Для этих целей введены дополнительные по сравнению с PostgreSQL имена для промежутков:

• second_microsecond — секунды и микросекунды, формат строки ‘s.m’;
• minute_microsecond — минуты и микросекунды, формат строки ‘m.m’;
• minute_second — минуты и секунды, формат строки ‘m:s’;
• hour_microsecond — часы и микросекунды, формат строки ‘h.m’;
• hour_second — часы, минуты и секунды, формат строки ‘h:m:s’;
• hour_minute — часы и минуты, формат строки ‘h:m’;
• day_microsecond — день и микросекунды, формат строки ‘d.m’;
• day_second — дни, часы, минуты и секунды, формат строки ‘d h:m:s’;
• day_minute — дни, часы и минуты, формат строки ‘d h:m’;
• day_hour — дни и часы, формат строки ‘d h’;
• year_month — года и месяцы, формат строки ‘y-m’.

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

-- интервал в три года
INTERVAL 3 year

-- интервал в 3 дня, 7 часов,
-- 7 минут и 5 секунд
INTERVAL '3 7:07:05' day_second

-- пример интервала в запросе
-- выборка интервала в два дня
-- ошибка, столбец не может быть типа INTERVAL
select INTERVAL '2 day'; 

-- правильно, к дате прибавляем интервал
select date '2009-01-01'+INTERVAL '3 7:07:05' day_second

Выражения и операции

Для построения выражений SQL включает стандартные операции, ряд дополнительных предикатов
(булевских конструкций) и функций. В MySQL для встроенных функций между именем и открывающей
скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД.
Oracle не поддерживает логические выражения в перечислении select.

cтроковые операции

|| — соединение строк, в некоторых СУБД операнды автоматически преобразуются в
строковый тип. В MS Access используется &

select 'hello'||' world' 
select 'hello'||' world' from dual -- для Oracle

алгебраические операции

• + — сложение;
• — — вычитание;
• * — умножение;
• / — деление;
• mod — остаток от деления. Oracle: mod(6,2). MySql: 6 mod 2.

Операции + и — также используются при работе со временем и интервалами.
В Oracle и PostgreSQL возможна разница между датами.
Результат возвращается в виде интервала в днях. Ниже приведен пример добавления к дате
интервала.

-- для PostgreSQL
select date '2009-01-01'+INTERVAL '3 7:07:05'

-- для Oracle
select date '2009-01-01' + 
  INTERVAL '3 7:07:05' day to second 
from dual;

-- для MySQL
select date '2009-01-01'+
  INTERVAL '3 7:07:05' day_second

Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.

-- для Oracle, 1 интерпретируется как день
select date '2009-01-01'+1 from dual

-- для PostgreSQL, 1 интерпретируется как день
select date '2009-01-01'+1

-- для MySQL, 1 интерпретируется как год
select date '2009-01-01'+1

операции отношения

• < — меньше;
• <= — меньше либо равно;
• > — больше;
• >= — больше либо равно;
• = — равно;
• <>,!= — не равно;

логические операции и предикаты

• and — логическое и;
• or — логическое или;
• nor — отрицание;
• between — определяет, находится ли значение в указанном диапазоне:

  выражение BETWEEN значение_с AND значение_по

• exists — определяет есть ли в указанной выборке хотя бы одна запись

  EXISTS (select ...)
  Для скорости в подзапросе обычно выбирают константу, а не поля записей, так
  как в данном случае нам важны не данные, а факт существования записей;

• in — определяет, входит ли указанное значение в указанное множество:

  выражение IN (значение1,...,значениеn)

  В качестве множества значений может служить корректная выборка

  выражение IN (select ...)

• is null — является ли указанное выражение NULL значением:

  выражение IS NULL

• like — определяет, удовлетворяет ли строка указанному шаблону:

  строковое_выражение LIKE шаблон [ESCAPE еск_символ]
  Знак % в шаблоне интерпретируется как строка любой длины, знак _
  как любой символ. В конструкции ESCAPE еск_символ указывается символ ESCAPE
  последовательности, который отменит обычную интерпретацию символов ‘_’ и ‘%’.
  В последних стандартах включены предикаты SIMILAR и LIKE_REGEX расширяющие возможности
  LIKE, используя в качестве шаблона регулярные выражения.

условные выражения

• case — условный оператор, имеющий следующий синтаксис:

  CASE WHEN условие THEN результат
       [WHEN условиеn THEN результатn]
       [ELSE результат_по_умолчанию]
  END

• decode(expr,s1,r1[,sn,rn][,defr]) — сравнивает выражение expr с каждым выражением si
  из списка. Если выражения равны то возвращается значение равное ri. Если ни одно
  из выражений в списке не равно expr, то возвращается defr или NULL, если defr не было указано.
  Эта функция доступна только в Oracle и в большинстве случае заменяет оператор CASE;
• coalesce(arg1,…,argn) — возвращает первый аргумент в списке не равный null. Для двух
  аргументов в Oracle можно воспользоваться функцией nvl;
• greatest(arg1,…,argn) — возвращает наибольший аргумент в списке;
• least(arg1,…,argn) — возвращает наименьший аргумент в списке;
• nullif((arg1,arg2) — возвращает null если два аргумента равны, иначе первый
  аргумент.

Ниже приведен пример использования выражения в запросе выбора данных.

-- для MySQL, PostresSQL 
-- в скобках наше выражение
select ('молоко' LIKE '%оло%') as result; 

-- эмулировать логический тип в запросах данных
-- для Oracle можно с помощью CASE
select case 
   -- в скобках наше условие
   when (2 BETWEEN 0 AND 3 ) 
   then 1 
   else 0 
   end as result from dual;

прочие операции

В каждой СУБД свой набор операций, выше были приведены наиболее употребительные.
Например, в PosgreSQL можно использовать и такие операции:

• ^ — возведение в степень;
• |/ — квадратный корень;
• ||/ — кубический корень;
• ! — постфиксный факториал;
• !! — префиксный факториал;
• @ — абсолютное значение.

Обзор функций

В арсенале каждой СУБД обязательно имеется набор встроенных функций для
обработки стандартных типов данных. В MySQL для встроенных функций между именем и
открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной
функции в БД. В некоторых СУБД, как Oracle, если функция не имеет аргументов,
то скобки можно опустить.

математические функции

• abs(x) — абсолютное значение;
• ceil(x) — наименьшее целое, которое не меньше аргумента;
• exp(x) — экспонента;
• floor(x) — наибольшее целое, которое не больше аргумента;
• ln(x) — натуральный логарифм;
• power(x, y) — возводит x в степень y;
• round(x [,y]) — округление x до y разрядов справа от десятичной точки. По умолчанию
  y равно 0;
• sign(x) — возвращает -1 для отрицательных значений x и 1 для положительных;
• sqrt(x) — квадратный корень;
• trunc(x [,y]) — усекает x до у десятичных разрядов. Если у равно 0
  (значение по умолчанию), то х усекается до целого числа. Если у меньше 0, от отбрасываются
  цифры слева от десятичной точки.

Тригонометрические функции работают с радианами:

• acos(x) — арккосинус;
• asin(x) — арксинус;
• atan(x) — арктангенс;
• cos(x) — косинус;
• sin(x) — синус;
• tan(x) — тангенс.

строковые функции

• ascii(string) — возвращает код первого символа, эта функция обратна функции CHR;
• chr(x) — возвращает символ с номером х, в MySQL это функция char;
• length(string) — возвращает длину строки;
• lower(string) — понижает регистр букв;
• upper(string) — повышает регистр букв;
• ltrim(string1[, string2]) — удаляет слева из первой строки все символы
  встречающиеся во второй строке. Если вторая строка отсутствует, то удаляются пробелы. В MySQL
  второй аргумент не поддерживается;
• rtrim(string1[, string2]) — аналогична функции ltrim, только удаление
  происходит справа;
• trim(string) — удаляет пробелы с обоих концов строки;
• lpad(string1, n[, string2]) — дополняет первую строку слева n символами из
  второй строки, при необходимости вторая строка дублируется. Если string2 не указана, то
  используется пробел;
• rpad(string1, n[, string2]) — аналогична функции lpad, только присоединение
  происходит справа;
• replace(string1, c1, c2) — заменяет все вхождения символа/подстроки c1 на c2.
  Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую
  строку (»). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке;
• instr(string1, string2[, a][, b]) — возвращает b вхождение строки string2
  в строке string1 начиная с позиции a. Если a отрицательно, то поиск происходит справа. По
  умолчанию a и b присваиваются значение 1. В MySQL последние два аргумента не поддерживаются. В
  PostgreSQL данной функции нет, однако ее реализация дана в документации, как раз для
  совместимости с Oracle;
• substr(string, pos, len) — возвращает подстрку с позиции pos и длины len.

работа с датами

В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:

• current_date — глобальная переменная содержащая текущую дату. Можно использовать и в других СУБД;
• trunc(d,s) — приводит дату к началу указанной временной отметки, например к началу месяца.
  В PostgreSQL есть аналогичная функция date_trunc(s,d). В MySQL для этих целей может
  использоваться функция date_format(d,s), но она возвращает результат в виде строки;
• add_months(d,n) — добавляет к дате указанное число месяцев;
• last_day(d) — последний день месяца, содержащегося в аргументе;
• months_between(d1,d2) — возвращает число месяцев между датами.

Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:

• квартал — q, quarter;
• год — yyyy, year;
• месяц — mm, month;
• неделя — ww, week;
• день — dd, day;
• час — hh, hour;
• минута — mi, minute.

Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов.
Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.

-- для PostgreSQL
select cast( (date_trunc('month', date '2009-01-15')
   + interval '1 month') as date) - 1 as d

-- для MySQL
select date ( date_format('2009-01-15','%Y-%m-01')) 
       + interval 1 month 
       - interval 1 day as d

Преобразование типов

Множество типов разрешенные для преобразования в констркуции CAST AS определяется
реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)],
char[(n)], date, datetime, decimal[(m[,d])], signed [integer], time, unsigned [integer].
А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со
множеством записей в массивы.

-- MySQL
select CAST('5.3' AS decimal)+2
select CAST( (select '5.3') AS decimal(6,2))+2.0

-- Oracle
select CAST('5,22' AS double precision) +2 from dual 

-- PostgreSQL
select CAST('5.22' AS double precision) +2 

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

select cast('tru' as boolean);
select cast('fa' as boolean);
-- ошибка, строка не похожа на 'true', 'false' 
-- и не равна строкам '1' или '0'
select cast('ok' as boolean) 

-- создадим функцию преобразования
-- просто указываем какие строки
-- понимать как true значение,
-- все остальные строки будут false значением
CREATE OR REPLACE FUNCTION to_bool(varchar) 
RETURNS boolean
AS $$ 
SELECT $1 = 'true' or $1 = 'tru' or 
       $1 = 'tr' or $1 = 't' 
       or $1 = '1' or $1='ok'$$
LANGUAGE SQL;

-- создаем преобразование типа varchar в boolean
CREATE CAST (varchar AS boolean) 
  WITH FUNCTION to_bool(varchar) 
  AS ASSIGNMENT;

-- теперь можно так
select cast ( 'ok'::varchar as boolean);
select cast( varchar 'ok' as boolean); 
select 'ok'::varchar::boolean;

-- уничтожение преобразования
DROP CAST IF EXISTS (varchar AS boolean) ;

В большинстве случае необходимо преобразование в строку либо из строки. Для этого случаяСУБД предоставляют дополнительные функции.

функции Oracle

• to_char(date [,format[,nlsparams]]) — дату в строку;
• to_char(number [,format[,nlsparams]]) — число в строку;
• to_date(string[,format[,nlsparams]]) — строку в дату;
• to_number( string [ ,format[, nlsparams] ]) — строку в число;
• to_timestamp(string, format) — строку во время.

В этих функциях format описание формата даты или числа, а nlsparams — национальные
параметры. Формат строки для даты задается следующими элементами:

• «» — вставляет указанный в ковычках текст;
• AD, A.D. — вставляет AD с точками или без точек;
• ВС, B.C. — вставляет ВС с точками или без точек;
• СС, SCC — вставляет век, SCC возвращает даты ВС как отрицательные числа;
• D — вставляет день недели;
• DAY — вставляет имя дня, дополненное пробелами до длины в девять символов;
• DD — вставляет день месяца;
• DDD — вставляет день года;
• DY1 — вставляет сокращенное название дня;
• FF2 — вставляет доли секунд вне зависимости от системы счисления;
• НН, НН12 — вставляет час дня (от 1 до 12);
• НН24 — вставляет час дня (от 0 до 23);
• MI — вставляет минуты;
• MM — вставляет номер месяца;
• MOMn — вставляет сокращенное название месяца;
• MONTHn — вставляет название месяца, дополненное пробелами до девяти символов;
• RM — вставляет месяц римскими цифрами;
• RR — вставляет две последние цифры года;
• RRRR — вставляет весь год;
• SS — вставляет секунды;
• SSSSS — вставляет число секунд с полуночи;
• WW — вставляет номер недели года (неделя — 7 дней от первого числа, а не от понедельника до воскресенья);
• W — вставляет номер недели месяца;
• Y.YYY — вставляет год с запятой в указанной позиции;
• YEAR, SYEAR — вставляет год, SYEAR возвращает даты ВС как отрицательные числа;
• YYYY, SYYYY — вставляет год из четырех цифр, SYYYY возвращает даты ВС как отрицательные числа;
• YYY, YY, Y — вставляет соответствующее число последних цифр года.

Формат числовой строки задается следующими элементами:

• $ — вставляет знак доллара перед числом;
• В — вставляет пробелы для целой части десятичного числа, если она равна нулю;
• MI — вставляет знак минус в конце (например, ‘999.999mi’);
• S — вставляет знак числа в начале или в конце (например,’s9999′ или ‘9999s’);
• PR — записывает отрицательное число в уголвых скобках (например,’999.999pr’);
• D — вставляет разделитель десятичной точки в указанной позиции (например, ‘999D999’);
• G — вставляет групповой разделитель в указанной позиции (например,’9G999G999′). При этом дробная часть числа отбрасывается;
• С — вставляет ISO идентификатор валюты в начале или в конце числа (например, ‘с9999’ или ‘9999с’);
• L — вставляет локальный символ валюты в в начале или в конце числа (например, ‘l9999’ или ‘9999l’);
• , — вставляет запятую в указанной позиции вне зависимости от группового разделителя;
• . — вставляет десятичную точку в указанной позиции вне зависимости от разделителя десятичной точки;
• V — возвращает значение, умноженное на 10^n, где n равно числу девяток после V. В случае необходимости это значение округляется;
• ЕЕЕЕ — 9.99ЕЕЕЕ возвращает значение в экспоненциальной форме записи;
• RM — RM значение будет записано римскими цифрами в верхнем регистре;
• rm — rm значение будет записано римскими цифрами в нижнем регистре;
• 0 — вставляет нули, вместо пробелов в начале строки или в конце, например,
  9990 вставляет нули, вместо пробелов в конце строки;
• 9 — каждая 9 определяет значащую цифру.

select to_char(sysdate, 
 '"системное время: "DD-MON-YY hh24.mi:ss CC "век"') 
 as c
   from dual;

select to_date('01012009','ddmmyyyy') as c 
   from dual;

select to_char(-10000,'99G999D99L',
  'NLS_NUMERIC_CHARACTERS = '',.''
   NLS_CURRENCY = ''baks'' ') as c
    from dual;

select to_char(9.12345,'099.99') as c 
    from dual    

функции PostgreSQL

• to_char(timestamp, format) — время в строку;
• to_char(interval, format) — интервал времени в строку;
• to_char(number, format) — число в строку;
• to_date(str, format) — строку в дату;
• to_number(str, format) — строку в число;
• to_timestamp(str, format) — строку во время.

Основные элементы форматирования совпадают с Oracle.

функции MySQL

При хранении даты в MySQL под типом Date (), она имеет формат 2011-07-11 (год-месяц-день). В некоторых случаях даже не имея разделителя 20110711.

Поскольку в русскоязычных странах более привычным к восприятию считается формат 11.07.2011 (день.месяц.год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.

Преобразовать дату можно несколькими способами.

1. при помощи php кода
2. воспользовавшись командой DATE_FORMAT () при выборке из базы.

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

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

Рассмотрим пример выполнения:

Допустим существует таблица message, которая содержит ячейку send_data с датой в формате 2011-07-11.

Для извлечения и преобразования даты напишем следующий код:

$message = mysql_fetch_array(mysql_query("SELECT DATE_FORMAT(send_data, '%e.%m.%Y') FROM message"));

Далее в том месте где необходимо вывести преобразованную дату, выводим массив $message любой, удобной для вас командой:

echo $message['0'];

к примеру если в send_data находится 2011-05-03 то мы получим 03.05.2011.

Номер индекса в массиве $message указываем каким по счету начиная от 0, в команде SELECT извлекается необходимое значение с преобразованной датой. К примеру при запросе:

$message = mysql_fetch_array(mysql_query("SELECT title, text, DATE_FORMAT(send_data, '%e.%m.%Y') FROM message"));

вывод даты будет осуществляться с индексом 2:

echo $message['2'];

Преобразовать дату при помощи DATE_FORMAT() можно в любой вид и очередность при помощи подстановки ключей.

• date_format(date,format) — дату в строку;
• time_format(time,format) — время в строку;
• format(number,precision) — число в cтроку типа ‘#,###,###.##’,
  где число знаков определяется вторым аргументом.

Ниже приведен список основных элементов форматирования для даты и времени:

• %c — месяц числом;
• %d — день месяца;
• %H — часы (от 0 до 24);
• %h — часы (1 до 12);
• %i — минуты;
• %s — секунды;
• %T — время в формате «hh:mm:ss»;
• %Y — год, четыре цифры;
• %y — год, две цифры.

select date_format(date '2010-02-01',
         '%c месяца %d дней %Y год') as c 

От автора: сегодня мы поговорим о том, как работают в SQL функции даты. В следующей таблице приведен список всех важных функций, связанных с датой и временем, которые доступны. Существуют и другие, поддерживаемые различными СУБД. Данный список представляет функции, доступные в СУБД MySQL.

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

При вызове со вторым аргументом, заданным в виде INTERVAL, функция ADDDATE() является синонимом DATE_ADD(). Связанная функция SUBDATE() является синонимом DATE_SUB(). Информацию об аргументе блока INTERVAL см. в разделе DATE_ADD().

При вызове со вторым аргументом, заданным в днях, MySQL рассматривает это как целое число дней для добавления в выражение.

ADDTIME(expr1,expr2)

ADDTIME () добавляет expr2 к expr1 и возвращает результат. Expr1 является выражением времени или даты и времени, в то время как expr2 является выражением времени.

CONVERT_TZ(dt,from_tz,to_tz)

Преобразует значение даты и времени dt из часового пояса, заданного в from_tz, в часовой пояс, заданный в to_tz, и возвращает полученное значение. Эта функция возвращает NULL, если аргументы недействительны.

CURDATE()

Возвращает текущую дату как значение в формате «YYYY-MM-DD» или YYYYMMDD, в зависимости от того, используется ли эта функция в строковом или в числовом контексте.

CURRENT_DATE и CURRENT_DATE()

CURRENT_DATE и CURRENT_DATE() являются синонимами для CURDATE()

CURTIME()

Возвращает текущее время как значение в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или в числовом контексте. Значение выражается для текущего часового пояса.

CURRENT_TIME и CURRENT_TIME()

CURRENT_TIME и CURRENT_TIME() являются синонимами для CURTIME().

CURRENT_TIMESTAMP и CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP и CURRENT_TIMESTAMP() являются синонимами для NOW().

DATE(expr)

Извлекает часть, относящуюся к дате, из выражения даты или даты и времени expr.

DATEDIFF(expr1,expr2)

DATEDIFF() возвращает expr1.expr2, выраженное как количество дней между двумя датами. И expr1, и expr2 являются выражениями даты или даты и времени. В расчетах используются только части относящиеся к дате.

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

Эти функции выполняют арифметические операции с датами. date представлено как значение DATETIME или DATE, указывающее начальную дату. expr представляет собой выражение, определяющее значение интервала, который нужно добавить или вычесть из исходной даты. expr — это строка; она может начинаться с «-» для отрицательных интервалов.

unit является ключевым словом, указывающим единицы измерения для выражения. Ключевое слово INTERVAL и обозначение единиц не чувствительны к регистру. В следующей таблице показана ожидаемая форма аргумента expr для каждого значения единицы измерения.

Значения QUARTER и WEEK доступны в MySQL начиная с версии 5.0.0.

DATE_FORMAT (date,format)

Эта команда задает формат значения date в соответствии с указанной строкой format. В строке format могут использоваться следующие указатели. Перед указателями формата необходимо добавлять символ ‘%’.

DATE_SUB(date,INTERVAL expr unit)

Аналогично функции DATE_ADD ().

DAY(date)

DAY() является синонимом функции DAYOFMONTH().

DAYNAME(date)

Возвращает день недели для указанной даты.

DAYOFMONTH(date)

Возвращает день месяца для указанной даты в диапазоне от 0 до 31.

DAYOFWEEK(date)

Возвращает индекс дня недели (1 = Sunday, 2 = Monday, ., 7 = Saturday). Эти значения индекса соответствуют стандарту ODBC.

DAYOFYEAR(date)

Возвращает день года для указанной даты в диапазоне от 1 до 366.

EXTRACT(unit FROM date)

Функция EXTRACT() использует те же типы указателей единиц измерения, что и DATE_ADD() или DATE_SUB(), но не выполняет арифметические операции с датами, а извлекает из даты часть относящуюся к указателю единиц измерения.

FROM_DAYS(N)

Возвращается значение DATE с учетом числа дней N.

Примечание. Используйте FROM_DAYS() для старых дат осторожно. Функция не предназначена для работы со значениями дат до введения григорианского календаря (1582).

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Возвращает представление аргумента unix_timestamp как значение в формате «YYYY-MM-DD HH: MM: SS или YYYYMMDDHHMMSS» в зависимости от того, используется ли эта функция в строковом или в числовом контексте. Значение выражается в текущем часовом поясе. Параметр unix_timestamp является внутренним значением метки времени, которое создается функцией UNIX_TIMESTAMP().

Если format указано, результат форматируется в соответствии со строкой format, которая используется так же, как описано в разделе DATE_FORMAT().

HOUR(time)

Возвращает часы из указанного времени. Диапазон возвращаемого значения составляет от 0 до 23. Однако диапазон значений TIME на самом деле намного больше, поэтому HOUR может возвращать значения, превышающие 23.

LAST_DAY(date)

Принимает значение даты или даты и времени и возвращает значение, соответствующее последнему дню месяца. Возвращает NULL, если аргумент недействителен.

LOCALTIME и LOCALTIME()

LOCALTIME и LOCALTIME() являются синонимами для NOW().

LOCALTIMESTAMP и LOCALTIMESTAMP()

LOCALTIMESTAMP и LOCALTIMESTAMP() являются синонимами для NOW().

MAKEDATE(year,dayofyear)

Возвращает значения даты, заданного года и дня года. Значение dayofyear должно быть больше 0 или результат будет NULL.

MAKETIME(hour,minute,second)

Возвращает значение времени, рассчитанное из аргументов hour, minute и second.

MICROSECOND(expr)

Возвращает микросекунды из выражения времени или выражения datetime(expr) в виде числа в диапазоне от 0 до 999999.

MINUTE(time)

Возвращает минуты для указанного времени, в диапазоне от 0 до 59.

MONTH(date)

Возвращает месяц для указанной даты в диапазоне от 0 до 12.

MONTHNAME(date)

Возвращает полное название месяца для указанной даты.

NOW()

Возвращает текущую дату и время как значение в формате «YYYY-MM-DD HH: MM: SS» или YYYYMMDDHHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте. Это значение выражается в текущем часовом поясе.

PERIOD_ADD(P,N)

Добавляет N месяцев к периоду P (в формате YYMM или YYYYMM). Возвращает значение в формате YYYYMM. Обратите внимание, что аргумент периода P не является значением даты.

PERIOD_DIFF(P1,P2)

Возвращает количество месяцев между периодами P1 и P2. Периоды P1 и P2 должны указываться в формате YYMM или YYYYMM. Обратите внимание, что аргументы периодов P1 и P2 не являются значениями даты.

QUARTER(date)

Возвращает квартал года для указанной даты в диапазоне от 1 до 4.

SECOND(time)

Возвращает значение секунд для времени в диапазоне от 0 до 59.

SEC_TO_TIME(seconds)

Возвращает аргумент seconds, преобразованный в часы, минуты и секунды в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте.

STR_TO_DATE(str,format)

Это инверсивная функция к функции DATE_FORMAT(). Она принимает строку str и строку формата format. Функция STR_TO_DATE() возвращает значение DATETIME, если строка формата содержит как дату, так и время. В противном случае она возвращает значение DATE или TIME, если строка содержит только дату или время.

SUBDATE(date,INTERVAL expr unit) и SUBDATE(expr,days)

Если SUBDATE() вызывается со вторым аргументом, заданным в виде INTERVAL, функция является синонимом DATE_SUB(). Информацию об аргументе INTERVAL смотрите в разделе DATE_ADD().

SUBTIME(expr1,expr2)

Функция SUBTIME() возвращает expr1. expr2 выражается как значение в том же формате, что и expr1. Значение expr1 является выражением времени или даты и времени, а значение expr2 является выражением времени.

SYSDATE()

Возвращает текущую дату и время как значение в формате «YYYY-MM-DD HH: MM: SS» или YYYYMMDDHHMMSS, в зависимости от того, используется ли функция в строковом или в числовом контексте.

TIME(expr)

Извлекает часть, относящуюся ко времени, выражения expr и возвращает его в виде строки.

TIMEDIFF(expr1,expr2)

Функция TIMEDIFF() возвращает expr1 . expr2 выражается как значение времени. Значения expr1 и expr2 представляют собой выражения времени или даты и времени, но оба они должны быть одного типа.

TIMESTAMP (expr), TIMESTAMP (expr1, expr2)

С одним указанным аргументом эта функция возвращает выражение даты или даты и времени expr, как значение даты и времени. С двумя аргументами она добавляет выражение времени expr2 к выражению даты или даты и времени expr1 и возвращает результат как значение даты и времени.

TIMESTAMPADD(unit,interval,datetime_expr)

Эта функция добавляет целочисленное выражение interval к выражению даты или времени datetime_expr. Единицы измерения для интервала задаются аргументом unit, который может принимать одно из следующих значений:

Значение unit может быть указано с использованием одного из ключевых слов, как было показано выше, или с префиксом SQL_TSI_. Например, DAY и SQL_TSI_DAY являются действительными значениями.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Возвращает целочисленную разницу между выражениями даты или даты и времени datetime_expr1 и datetime_expr2. Единицы измерения для результата задаются аргументом unit. Действительными для аргумента unit являются те же значения, которые были перечислены в описании функции TIMESTAMPADD().

TIME_FORMAT(time,format)

Эта функция используется так же, как и функция DATE_FORMAT(), но строка format может содержать указатели формата только для часов, минут и секунд.

Если значение времени содержит часть, относящуюся к часам, которая больше 23, указатели формата часов %H и %k дают значение, большее, чем обычный диапазон от 0 до 23. Другие указатели формата часов дают значение часа 12 по модулю.

TIME_TO_SEC(time)

Возвращает аргумент time, преобразованный в секунды.

TO_DAYS(date)

Возвращает номер дня (количество дней с 0-го года) для заданной даты date.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

Если эта функция вызывается без аргумента, она возвращает временную метку Unix (секунды с «1970-01-01 00:00:00» UTC), как целое положительное число. Если UNIX_TIMESTAMP() вызывается с аргументом date, она возвращает значение аргумента, выраженное в секундах с «1970-01-01 00:00:00» UTC. date может быть строкой DATE, строкой DATETIME, TIMESTAMP или числом в формате YYMMDD или YYYYMMDD.

UTC_DATE, UTC_DATE()

Возвращает текущую дату UTC как значение в формате «YYYY-MM-DD» или YYYYMMDD, в зависимости от того, используется ли эта функция в строковом или числовом контексте.

UTC_TIME, UTC_TIME()

Возвращает текущее время UTC как значение в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте.

UTC_TIMESTAMP, UTC_TIMESTAMP()

Возвращает текущую дату и время UTC как значение «YYYY-MM-DD HH: MM: SS» или в формате YYYYMMDDHHMMSS, в зависимости от того, используется ли эта функция в строковом или в числовом контексте.

WEEK(date[,mode])

Эта функция возвращает номер недели для заданной даты date. Форма WEEK() с двумя аргументами позволяет указать, будет ли неделя начинаться в воскресенье или в понедельник, и должно ли возвращаемое значение находиться в диапазоне от 0 до 53 или от 1 до 53. Если аргумент mode опущен, используется значение системной переменной default_week_format

WEEKDAY(date)

Возвращает индекс дня недели для заданной даты date (0 = понедельник, 1 = вторник, 6 = воскресенье).

WEEKOFYEAR(date)

Возвращает календарную неделю для заданной даты date как число в диапазоне от 1 до 53. WEEKOFYEAR() — это функция совместимости, эквивалентная WEEK(date,3).

YEAR(date)

Возвращает год для заданной даты date в диапазоне от 1000 до 9999 или 0 для даты .zero.

YEARWEEK(date), YEARWEEK(date,mode)

Возвращает год и неделю для заданной даты date. Параметр mode работает точно так же, как аргумент mode для функции WEEK(). Год в результате может отличаться от года в аргументе даты для первой и последней недели года.

Примечание. Номер недели отличается от того, что вернет функция WEEK() (0) для необязательных аргументов 0 или 1, так как WEEK() вернет неделю в контексте данного года.

Источник: //www.tutorialspoint.com/

Редакция: Команда webformyself.

Microsoft SQL дата имеет огромное значение. Не зная ее основ, невозможно преуспеть ни в одном проекте. Предлагаем вспомнить основные термины, поговорить об особенностях работы с ними. Для тех, кто не знаком с этими понятиями в SQL Server, данная статья – прекрасный повод выучить их.

Почему важно разбираться

MS SQL дата применяется для хранения в базе данных различный сведений, связанных с понятиями даты и времени. Таковыми являются сведения, когда добавлялись или удалялись какие-либо данные, регистрировались пользователи, осуществлялась авторизация и т.д.

Эти значения очень важны в MS SQL Server. Невозможно достичь успеха ни в одном проекте, не зная операторов MS SQL дата. С ними приходится работать во всех сервисах. Чаще всего, это определение временных интервалов между определенными датами.

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

Используемые функции даты

Воспользовавшись специальными функциями, можно получить текущие (Current) значения:

• NOW() – пары даты и времени;
• CURDATE() — возвращает текущую дату (Missing Time);
• CURTIME() — исключительно время (Missing Date).

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

Операторы

Для применения MS SQL дата необходимо разбираться в операторах:

Функции:

• DATE_FORMAT() — для разных способов вывода сведений о времени.

Указывает из чего состоит:

• DATE() — из даты со временем.

Возврат значений:

• DATEDIFF() — временного отрезка между определенными датами.
• EXTRACT() —  единого значения даты/времени.

Выполнения действий:

• DATE_ADD() — добавления определенного количества единиц времени до выборки;
• DATE_SUB() — вычисления указанного интервала от даты.

Популярные операции с ms sql дата

Рассмотрим наиболее популярные операции, осуществляемые с датами (Dates) и временем (Time):

Функции подсчета интервала

В таблице представлены специальные функции для нахождения интервалов между определенными датами:

Функции вычисления разных значений в SQL Server и иной полезной информации

В SQL Server существуют специальные функции, с помощью которых можно найти много полезной информации. Часть их представлена в таблице:

Возможные ошибки в работе

Рассмотрим самые распространенные ошибки, которые встречаются при работе с MS SQL дата и время в SQL Server.

1.      Как хранятся значения?

Большинство ошибок связано с тем, что разработчики не до конца понимают способы хранения MS SQL даты и времени в MS SQL Server. Проблема в том, что даже документация не даст ответов на этот вопрос, т.к. не раскрывает эту тему полностью.

Некоторые T-SQL-разработчики, только начинающие свой профессиональный путь, полагают, что данные значения сохраняются в виде, привычном для человека («01-07-2021 11:15:22.167»). Однако это ошибочное мнение. Способ хранения MS SQL дата зависит от типа данных и может выражаться одним или несколькими целыми числами. Более понятно – дата/время хранится в виде чисел. Мнение, что они хранятся в форматированных строках – ошибочно.

Рассмотрим тип DATETIME. Согласно инструкций SQL Server данное значение сохраняется двумя целыми числами размеров в 8 байт (каждый по 4 байта):

• в первом – указывается день в диапазоне от 01.01.1753г. до 31.12.9999г.;
• во втором – указывается время в диапазоне от 00:00:00.000 до 23:59:59.997.

На это стоит обратить особое внимание. По умолчанию (нулевым днем) является значение 1 января 1900 года (1900-01-01 00:00:00.000). Отсюда следует, что даты раньше принятой, будут отрицательными числами, а позже – положительными. Так, 31.12.1899г. примет отрицательное значение (-1-й день), а 31.12.1900г. – положительное (364-й день).

Для хранения значения дата/время его нужно конвертировать в тип VARBINARY. А чтобы потом увидеть только дату либо только время, следует воспользоваться функцией SUBSTRING.

Проблемы могут возникнуть при указании различных языков (languages) и формата значений. Для корректной работы значение должно иметь структуру (Structure): YYYY-MM-DD HH при выбранном языке (language) British.

2.      Помнить о тех, кто живет в иных географических широтах

Довольно распространенная ошибка – конфигурация установленного экземпляра SQL Server для обслуживания лишь локальных потребителей. Это может стать причиной проблем при работе с этим типом данных.

Как сказано выше, SQL Server сохраняет SQL даты в виде целых чисел. Однако частенько конвертирует их, переводя из целых чисел в формат строк, более привычных для человека. Потому в SQL Server принято несколько правил и параметров, которые и определяют порядок интерпретации значений строк даты/время.

3.      Не позабыть о тех, кто живет в иных часовых поясах

При разработке приложений, которые охватывают не один регион в плане географии, разработчикам приходится сталкиваться с рядом проблем из-за часовых поясов. Неоднозначность таких типов данных – одна из них. Указанная SQL дата со временем практически не имеет смысла, пока не предоставлен механизм, обеспечивающий контекст.

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

Проблема получила решение, когда в SQL Server 2008 были внедрены:

• Data Type DATETIMEOFFSET. Его задача – упростить управление Date/Time. DATETIMEOFFSET в SQL Server сохраняет информацию, как и DATETIME2, а для часовых поясов (в отношении UTC) используется дополнительная пара байт;
• SWITCHOFFSET (функция системная), чтобы иметь возможность менять часовые пояса значений DATETIMEOFFSET в SQL Server.

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

Проблема для разработчиков состоит в следующем:

• не все регионы осуществляют подобные переходы;
• в регионах, которые осуществляют переходы, это происходит в разное время;
• из-за изменения законодательной базы, определяющей данные переходы, регулярно изменяются числа, в которые они осуществляются. Далее это будет еще более непредсказуемо;
• часовые пояса могут достаточно серьезно отличаться как в странах, так и в одном регионе или государстве.

А значит, чтобы обеспечить получение надежных значений даты/времени нельзя рассчитывать лишь на DATETIMEOFFSET. Смена поясов и практически лишенный управления переход между зимним и летним временем, создают проблему. Создаваемые приложения и аналитика обязаны включать логику, требующуюся для обработки подобных задач.

4.      Что выбрать: DATETIME2 или DATETIME?

Многие T-SQL-разработчики не часто пользуются DATETIME2. Хотя он был представлен еще в SQL Server 2008, они используют привычную DATETIME. Однако это не совсем оправданно, т.к. DATETIME2 обладает рядом достоинств:

• обладает более высокой точностью, т.к. для формата Time содержит после запятой до семи десятичных знаков, а DATETIME – лишь три. Здесь значения не округляются, а лишние обрезаются;
• позволяет контролировать точность Time, т.к. можно установить точность. Это позволит отрезать лишние знаки и сделать результат чище;
• если значение точности превышает 4, используется 8 байт, а если до 4, то занимает 6 байт. Это помогает экономить место, что особенно ценно при работе с огромными массивами данных;
• позволяет работать со значительно большим диапазоном дат (с 1 января 0001 года). В то время как в значение DATETIME можно лишь с 01.01.1753г.

Отметим, что при использовании старых приложений и систем, внедрение DATETIME2 может стать проблематичным. Но в процессе появления новых, при условии работы в SQL Server 2008 и старше, гораздо удобнее применять DATETIME2. Исключением могут стать технологии, не способные обработать его.

5.      Игнорирование округления

Необходимо соблюдать осторожность при использовании таких Data Type, как DATETIME и SMALLDATETIME. Округления могут привести к потере значительных временных промежутков. Их использование может спровоцировать получение данных, способных неожиданно отразиться на результатах.

Гораздо надежнее использовать DATETIME2, когда это возможно.

6.      Выполнение лишней работы

Чтобы из полной даты отсечь время, до SQL Server 2008 нужно было приложить усилия. Однако в версии 2008 может использоваться тип DATE. С его помощью достаточно просто конвертировать DATETIME2 в DATE и получить необходимый итог: одну только дату без упоминания времени.

Если необходимо оставить только временной показатель, то исходную информацию следует конвертировать в Data Type TIME. В этом случае оператор SELECT позволит получить только время. Использование таких типов данных, как DATE и TIME – наилучшие способы получения отдельных значений из первоначальных дата/время.

Для обнуления также можно использовать указанные выше DATEDIFF и DATEADD.

7.      Непонимание принципов работы функции DATEDIFF

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

В процессе определения числа часов/ минут, прошедших между двумя показателями, полученный результат может отличаться от настоящего в разы. Так, при разнице в 1 секунду, SELECT DATEDIFF может вернуть разницу в одном часе или одной минуте. Такой же будет разница с днями, месяцами и т.д.

Основная проблема не в самой функции DATEDIFF. А в непонимании основ ее работы. SQL Server обращает внимание только на указанную даты, не более. Потому, при указании месяца, сопоставляться будут годы с месяцами, но никак не больше. Разница может составлять одну секунду, но SQL Server на это не обратить внимание.

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

SELECT DATEDIFF(ss, @a, @b)/60.0000

Работа с MS SQL дата не представляет сложности. Вместо вычисления периодов с помощью PHP, можно выполнять эти действия еще при выполнении SQL запросов. Это позволит быстрее получать требуемую выборку данных. Вспоминайте либо изучайте материал по MS SQL дата. При возникновении вопросов – обязательно задавайте их.

Также приглашаем на специальный курс по MS SQL в Otus.