Funkcje bazy danych MySQL 5.5 - Data i Czas

W poniższym blogu prezentuję omówienie poleceń baz danych MYSQL w wersji 5.5 dotyczących daty i czasu. Opracowanie jest w j. polskim zachęcam do lektury. Opracowanie opiera się o własne testy oraz o manual do mysql 5.5.0

*Timestamp jest formatem przedstawiającym ile sekund upłynęło od 1 stycznia 1970 00:00:00. Np dla daty: 2010-05-04 12:03:27  Timestamp wynosi (1272967407)

ADDDATE(), DATE_ADD()

Funkcja dodaje okresy do dat,

Są 2 sposoby formatowania polecenia:

ADDDATE('data',interwal [ilość][jednostka])

np adddate('2010-03-02', interval 31 day)

dopuszczalne jednostki: day,month,year,week

ADDDATE([data],[ilość dni])   

np ADDDATE(now(),20 ),ADDDATE('2010-05-04',20 )

test wykonałem komendą:
update test set rok=adddate('2010-03-02', interval xx day) where [warunek];

Test wykonałem na polu typu DATETIME stąd wartości godzin po modyfikowanych datach, komenda modyfikuje tylko daty

* odejmowanie dni powoduje wyzerowanie godziny

** dodawanie dni do konkretnej daty formatu RRRR-MM-DD w polu DATETIME zapisuje wynik z godziną 00:00:00

Poniższa tabela prezentuje jednostki i format wartości czasu jaki jest dodawany od dat

Prezentacja:

SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2009-01-01 00:00:00'
SELECT INTERVAL 1 DAY + '2008-12-31';
        -> '2009-01-01'
SELECT '2005-01-01' - INTERVAL 1 SECOND;
        -> '2004-12-31 23:59:59'
SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
        -> '2001-01-01 00:00:00'
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
        -> '2011-01-01 23:59:59'
SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2004-12-30 22:58:59'
SELECT DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

• pole [Ilość] traktowane jest jako napis więc ilość czasu o jaką zwiększamy możemy zdefiniować jako 6/4 co przy typie HOUR_MINUTE spowoduje dodanie 1 godziny i 5000 .minut

SELECT 6/4;
        -> 1.5000
SELECT DATE_ADD('2009-01-01', INTERVAL 6/4 HOUR_MINUTE);
        -> '2009-01-04 12:20:00'

• aby się upewnić że podane dane zostaną zinterpretowane tak jak sobie tego życzysz można użyć operacji CAST() aby 6/4 zostało potraktowane jako 1 godzina 5 minut

konwertujemy to do wartości dziesiętnej z jedną cyfrą ułamkową:

SELECT CAST(6/4 AS DECIMAL(3,1));
        -> 1.5
SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
        -> '1970-01-01 13:05:00'

• Jeśli masz zamiar dodać lub odjąć od dat okres zawierający część czasu ( godzina, minuta itd) wynik jest automatycznie konwertowany na wartość typu DATETIME

ADDTIME()

 informacje przeczytaj również z komendy adddate()!

Funkcja dodaje interwały czasu dni i godziny (w dniach) do dat,test wykonałem komendą:

update test set rok=addtime('2010-03-02', interval 31 day) where [warunek];

Test wykonałem na polu typu DATETIME

* daty wsteczne, powodujące zmianę miesiąca na wcześniejszy powodują przestawienie godziny na 01:00:00

CURTIME()

test wykonałem poleceniem  

update test set godzina= curtime() where id=5;

należy pamiętać że komenda działa tylko w kolumnach typu TIME

wykonana na polu typu DATETIME zwróciła wynik 0000-00-00 00:00:00

Zwraca wartość bieżącego czasu w 2 formatach: 'HH:MM:SS' lub HHMMSS.uuuuuu

SELECT CURTIME();
        -> '23:50:26'
SELECT CURTIME() + 0;
        -> 235026.000000

CURDATE()

test wykonałem komendą:  

update test set aktualnadata= curdate() where id = 5;

Wykonanie jej na polu typu TIME nie zwraca wartości

Wykonana na polu DATETIME zwraca wynik 2010-05-04 00:00:00

i oczywiście w polu DATE zwraca 2010-05-04

Zwraca wartości w 2 formatach:'YYYY-MM-DD' lub YYYYMMDD

SELECT CURDATE();
        -> '2010-05-05'
SELECT CURDATE() + 0;
        -> 20100505

DATE()

Komenda zwraca datę z wyrażenia lub funkcji, której wynikiem jest data lub data i czas

test wykonano komendą SELECT DATE('2010-05-05 14:10:14');
EFEKT -> 2010-05-05
test wykonano komendą SELECT date(now());
EFEKT -> 2010-05-05

DATE_FORMAT()

Formatuje datę zgodnie ze zdefiniowanym formatem

tabela dostępnych formatów znajduje się poniżej:

Przykłady:

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'

SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'

SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'

DATE_SUB()

patrz opis ADDDATE()

DAY() , DAYOFMONTH()

Zwraca dzień miesiąca z daty, przyjmuje wartości  1...31 lub 0 dla dat postaci 0000-00-00 2008-00-00 itp

SELECT DAYOFMONTH('2007-02-03');
        -> 3

DAYNAME()

Zwraca nazwę dnia tygodnia ( w j. angielskim) z daty

SELECT DAYNAME('2007-02-03');
         -> 'Saturday'

DAYOFWEEK()

Zwraca dzień tygodnia liczbowo z daty gdzie  1=Sunday (Niedziela).....7=Saturday (Sobota)

SELECT DAYOFWEEK('2010-05-05');
    -> 4
SELECT DAYOFWEEK(now());
    -> 4

DAYOFYEAR()

Zwraca dzień roku 1...366 z daty 

SELECT DAYOFYWAR('2010-05-05');
    -> 125
SELECT DAYOFYEAR(now());
    -> 125

EXTRACT()

Wyciąga zdefiniowaną część z daty, używa tych samych elementów definiujących jednostki co ADDDATE() DATESUB() itd.

SELECT EXTRACT(YEAR FROM '2009-07-02');
       -> 2009
SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
       -> 200907
SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
       -> 20102
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
        -> 123

FROM_DAYS()

polecenie zwraca rok od roku 0000-00-00 po upłynięciu ilości dni podanych w argumencie

SELECT FROM_DAYS(366);

    ->0001-01-01

SELECT FROM_DAYS(734262);

    -> 2010-05-05

FROM_UNIXTIME()

Zwraca reprezentację argumentu unix_timestamp   w formatach  'YYYY-MM-DD HH:MM:SS' lub YYYYMMDDHHMMSS.uuuuuuw zależności jaka funkcja jest użyta w funkcji lub  numerycznej zawartości. 

Jeżeli format jest zdefiniowany wynik będzie sformatowany zgodnie z napisem formatującym, który definiuje się analogicznie z formatami funkcji DATE_FORMAT()

SELECT FROM_UNIXTIME(1196440219);
        -> '2007-11-30 10:30:19'
SELECT FROM_UNIXTIME(1196440219) + 0;
        -> 20071130103019.000000
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
        -> '2007 30th November 10:30:59 2007'

GET_FORMAT()

Funkcja jest używana w połączeniu z DATE_FORMAT() i STR_TO_DATE(). Określa sposób w jaki ma być zaprezentowana data.

TIMESTAMP może również być używany jako pierwszy argument w funkcji GET_FORMAT, w tym przypadku funkcja zwróci te same wartości co DATETIME

HOUR()

funkcja zwraca godzinę z czasu. może przyjmować wartości większe od 23.

SELECT HOUR('10:05:03');
        -> 10
SELECT HOUR('272:59:59');
        -> 272

LAST_DAY()

Zwraca datę odpowiadającą ostatniemu dniu miesiąca z daty będącej argumentem funkcji. Jeśli argument jest błędną datą zwracana jest wartość NULL

SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
SELECT LAST_DAY('2003-03-32');
        -> NULL

LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

są to synonimy funkcji NOW()

MAKEDATE()

MAKEDATE(rok,dzień roku)

Zwraca datę z argumentów - roku i dnia roku

SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
        -> '2011-01-31', '2011-02-01'
SELECT MAKEDATE(2011,365), MAKEDATE(2014,365);
        -> '2011-12-31', '2014-12-31'
SELECT MAKEDATE(2011,0);
        -> NULL

MAKETIME()

MAKETIME(godzina,minuta,sekunda)

SELECT MAKETIME(12,15,30);
        -> '12:15:30'

MINUTE() , MICROSECOND(),SECOND()

analogicznie do HOUR()

MONTH()

zwraca numerycznie numer miesiąca z daty:

SELECT MONTH('2008-02-03');
        -> 2

MONTHNAME()

zwraca nazwę miesiąca w j. angielskim

SELECT MONTHNAME('2008-02-03');
        -> 'February'

NOW()

Funkcja zwraca aktualną datę i godzinę

update test set aktualnadata= now() where id = 5;

SELECT NOW();
-> 2010-05-05 15:51:15

SELECT NOW()+0;
-> 20100505155135.000000

PERIOD_ADD()

PERIOD_ADD(p,n)

dodaje n miesięcy do daty p, format daty to RRRMM lub RRMM, argument p nie jest wartością daty

SELECT PERIOD_ADD(200801,2);
        -> 200803

PERIOD_DIFF()

PERIOD_DIFF(p1,p2)

zwraca różnicę w miesiącach z 2 dat, które powinny być formatu RRRRMM lub RRMM

SELECT PERIOD_DIFF(200802,200703);
        -> 11

QUARTER() 

analogicznie do MONTH()

SELECT QUARTER('2008-04-01');
        -> 2

SEC_TO_TIME()

SEC_TO_TIME(sekundy)

Zwraca godzinę jaką tworzy podana w argumencie liczba sekund.

SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
SELECT SEC_TO_TIME(2378) + 0;
        -> 3938

STR_TO_DATE()

STR_TO_DATE(str,format)

Jest odwrotnością DATE_FORMAT(). Pobiera napis (str) oraz format a zwraca  wartość typu DATETIME jeżeli napis (str) posiada obie części - datę i czas lub zwraca wartość DATE lub TIME jesli str zawiera tylko jedną z nich. Jeżeli któryś z parametrów jest niewłaściwy zwracana jest wartość NULL

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'

Skanowanie rozpoczyna się na początku napisu (str) i zwraca błąd jeśli znaleziony format nie pasuje do str. Wszystkie dodatkowe znaki na końcu str poza formatem są ignorowane

SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
        -> '09:30:17'
SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
        -> NULL
SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
        -> '09:30:17'

Nieokreślona data lub okres czasowy konwertowane są na wartość 0, niekompletne lub częściowo niepoprawne części str powodują wyzerowanie całego bądź części wyniku.

SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

Daty zerowe bądź częściowo zerowe są dozwolone dopóki tryb SQL nie będzie zabraniał takich wartości

SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
        -> '0000-00-00'
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'

SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
        -> '2004-10-18'

SUBDATE()

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

kiedy używamy pierwszego zapisu SUBDATE() jest synonimem DATE_SUB

zastosowanie analogiczne z DATE_ADD tam też patrz przykłady

SUBTIME()

analogicznie z ADDTIME()

TIME()

Funkcja zwraca wartość godziny z podanego argumentu test wykonano komendą

insert into tabela (`kolumna`) values (time([argument liczbowy]))

* maksymalna wartość godziny jaką mozna zapisać używając funkcji time()

TIMEDIFF()

TIMEDIFF(expr1, expr2)

Podaje różnicę w wartościach czasu pomiędzy wyrażeniami expr1 (expression1) i expr 2, oba jednak muszą być tego samego typu:

SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
SELECT TIMEDIFF('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002');
-> '46:58:57.999999'

TIMESTAMP()

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
Z pojedynczym argumentem expr (expression) powoduje zwrócenie daty i godziny (w formacie DATETIME) natomiast przy 2 argumentach expr1 i expr2 powoduje wartości typu DATETIME sumy argumentów:

SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'

TIMESTAMPADD()

TIMESTAMPADD(unit,interval,datetime_expr)

Dodaje wartość całkowitą do daty dub wartości typu DATETIME. argument unit powinien być jednym z następujących:

MICROSECOND , SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
-> '2003-01-02 00:01:00'
SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
-> '2003-01-09'

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Podaje różnicę pomiędzy dwiema wartościami typu DATETIME. Wartości mogą być typu DATE lecz część czasowa będzie wtedy przyjęta za 00:00:00. Wynik jest wartością całkowitą wyrażoną w jednostkach unit:

MICROSECOND , SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

przykłady:
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885

TIME_FORMAT()

TIME_FORMAT(time,format)

komendy używa się jak DATE_FORMAT(), jednak format może zawierać formaty przypisane godzinom, minutom sekundom. Inne formaty powodują wyświetlenie wartości NULL.

Gdy ilość godzin jest większa niż23 formaty %H i %k powodują wyświetlenie wartości większej niż standardowy zakres 0...23, inny format powoduje wyświetlenie czasu modulo 12.

SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'

TIME_TO_SEC()

TIME_TO_SEC(time)

Zwraca argument czasu jako sekundy:

SELECT TIME_TO_SEC('22:23:00');
-> 80580
SELECT TIME_TO_SEC('00:39:38');
-> 2378

TO_DAYS( )

TO_DAYS(date)

Zwraca ilość dni od roku 0 do daty podanej jako argument np:

SELECT TO_DAYS(950501);
        -> 728779
SELECT TO_DAYS('2007-10-07');
        -> 733321

TO_SECONDS( )

TO_SECONDS(date) dostępne od MYSQL 5.5.0

Zwraca ilość sekund od roku 0 do daty podanej jako argument np:

SELECT TO_SECONDS(950501);
-> 62966505600
SELECT TO_SECONDS('2009-11-29');
-> 63426672000
SELECT TO_SECONDS('2009-11-29 13:43:32');
-> 63426721412
 

WEEK()

WEEK(date,mode)

Funkcja zwraca numer tygodnia dla daty. Dwa argumenty pozwalają na wybór typu tygodnia tzn czy ma zaczynać się od niedzieli czy poniedziałku, czy wartość ma być wyświetlana z zakresu 0...53 czy 1...53

SELECT WEEK('2008-02-20');

-> 7

SELECT WEEK('2008-02-20',0);

  -> 7  

SELECT WEEK('2008-02-20',1)

  -> 8

SELECT WEEK('2008-12-31',1);

  -> 53

WEEKDAY()

WEEKDAY(date)

Zwraca numer dnia tygodnia z podanej daty 0 - poniedziałek 6-niedziela

SELECT WEEKDAY('2008-02-03 22:23:00');
-> 6
SELECT WEEKDAY('2007-11-06');
-> 1

WEEKOFYEAR()

WEEKOFYEAR(date)

odpowiednik WEEK(date,3) zwraca numer tygodnia z podanej jako argument daty:

SELECT WEEKOFYEAR('2008-02-20');
-> 8

YEAR()

YEAR(date)
Zwraca wartość roku z daty zakres 1000....9999

SELECT YEAR('1987-01-01');
        -> 1987

Oryginalny text w j. angielskim  znajduje się TUTAJ
Jestem autorem tłumaczenia i jego powielanie , kopiowanie musi odbyć się za moją zgodą, kontakt znajdziecie w profilu