Curso de MariaDB Desde la Consola/Funciones de Fechas y Tiempo

De WikiCabal
< Curso de MariaDB Desde la Consola
(dif) ← Revisión anterior | Revisión actual (dif) | Revisión siguiente → (dif)
Ir a la navegación Ir a la búsqueda

Funciones de Fechas y Tiempo

Interval

 1 El directivo INTERVAL se puede utilizar para añadir o restar un 
 2 intervalo de tiempo a un DATETIME, DATE o TIME valor.
 3 La sintaxis es: 
 4 
 5 INTERVAL time_quantity Time_unit
 6 
 7 MariaDB [(none)]> SELECT '2015-10-21 12:15:30' +  INTERVAL 4 day;
 8 +-----------------------------------------+
 9 | '2015-10-21 12:15:30' +  INTERVAL 4 day |
10 +-----------------------------------------+
11 | 2015-10-25 12:15:30                     |
12 +-----------------------------------------+
13 1 row in set (0.01 sec)
14 
15 MariaDB [(none)]> SELECT '2014-05-17 08:44:21' - INTERVAL '7 1' DAY_HOUR;
16 +-------------------------------------------------+
17 | '2014-05-17 08:44:21' - INTERVAL '7 1' DAY_HOUR |
18 +-------------------------------------------------+
19 | 2014-05-10 07:44:21                             |
20 +-------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 Las siguientes unidades son válidas:
Undad Descripción
MICROSECOND Microseconds
SECOND Seconds
MINUTE Minutes
HOUR Hours
DAY Days
WEEK Weeks
MONTH Months
QUARTER Quarters
YEARYears
SECOND_MICROSECONDSeconds.Microseconds
MINUTE_MICROSECONDMinutes.Seconds.Microseconds
MINUTE_SECONDMinutes.Seconds
HOUR_MICROSECONDHours.Minutes.Seconds.Microseconds
HOUR_SECONDHours.Minutes.Seconds
HOUR_MINUTEHours.Minutes
DAY_MICROSECONDDays Hours.Minutes.Seconds.Microseconds
DAY_SECONDDays Hours.Minutes.Seconds
DAY_MINUTEDays Hours.Minutes
DAY_HOURDays Hours
YEAR_MONTHYears-Months

DATE_ADD ADDDATE

 1 Cuando se invoca con la forma INTERVAL del segundo argumento, ADDDATE () 
 2 es sinónimo de DATE_ADD (). Cuando se invoca con la forma DAY del segundo 
 3 argumento, MariaDB lo trata como un número entero de días a añadir a expr.
 4 
 5 MariaDB [(none)]> SELECT DATE_ADD('2015-10-25', INTERVAL 31 DAY);
 6 +-----------------------------------------+
 7 | DATE_ADD('2015-10-25', INTERVAL 31 DAY) |
 8 +-----------------------------------------+
 9 | 2015-11-25                              |
10 +-----------------------------------------+
11 1 row in set (0.00 sec)
12 
13 MariaDB [(none)]> SELECT ADDDATE('2015-10-25', INTERVAL 31 DAY);
14 +----------------------------------------+
15 | ADDDATE('2015-10-25', INTERVAL 31 DAY) |
16 +----------------------------------------+
17 | 2015-11-25                             |
18 +----------------------------------------+
19 1 row in set (0.00 sec)
20 
21 MariaDB [(none)]> SELECT ADDDATE('2015-10-25', 31);
22 +---------------------------+
23 | ADDDATE('2015-10-25', 31) |
24 +---------------------------+
25 | 2015-11-25                |
26 +---------------------------+
27 1 row in set (0.00 sec)
28 
29 MariaDB [(none)]> use Prueba1
30 Database changed
31 
32 MariaDB [Prueba1]> CREATE TABLE t1 (d DATETIME);
33 Query OK, 0 rows affected (0.25 sec)
34 
35 MariaDB [Prueba1]> INSERT INTO t1 VALUES
36     ->     ("2007-01-30 21:31:07"),
37     ->     ("1983-10-15 06:42:51"),
38     ->     ("2011-04-21 12:34:56"),
39     ->     ("2011-10-30 06:31:41"),
40     ->     ("2011-01-30 14:03:25"),
41     ->     ("2004-10-07 11:19:34");
42 Query OK, 6 rows affected (0.08 sec)
43 Records: 6  Duplicates: 0  Warnings: 0
44 
45 MariaDB [Prueba1]> SELECT d, ADDDATE(d, 10) from t1;
46 +---------------------+---------------------+
47 | d                   | ADDDATE(d, 10)      |
48 +---------------------+---------------------+
49 | 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
50 | 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
51 | 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
52 | 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
53 | 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
54 | 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
55 +---------------------+---------------------+
56 6 rows in set (0.01 sec)
57 
58 MariaDB [Prueba1]> SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
59 +---------------------+------------------------------+
60 | d                   | ADDDATE(d, INTERVAL 10 HOUR) |
61 +---------------------+------------------------------+
62 | 2007-01-30 21:31:07 | 2007-01-31 07:31:07          |
63 | 1983-10-15 06:42:51 | 1983-10-15 16:42:51          |
64 | 2011-04-21 12:34:56 | 2011-04-21 22:34:56          |
65 | 2011-10-30 06:31:41 | 2011-10-30 16:31:41          |
66 | 2011-01-30 14:03:25 | 2011-01-31 00:03:25          |
67 | 2004-10-07 11:19:34 | 2004-10-07 21:19:34          |
68 +---------------------+------------------------------+
69 6 rows in set (0.00 sec)
70 
71 MariaDB [Prueba1]> SELECT DATE_ADD('2015-12-31 23:59:59', INTERVAL 1 SECOND);
72 +----------------------------------------------------+
73 | DATE_ADD('2015-12-31 23:59:59', INTERVAL 1 SECOND) |
74 +----------------------------------------------------+
75 | 2016-01-01 00:00:00                                |
76 +----------------------------------------------------+
77 1 row in set (0.00 sec)
78 
79 MariaDB [Prueba1]> SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
80 +------------------------------------------------------------+
81 | DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) |
82 +------------------------------------------------------------+
83 | 1899-12-30 14:00:00                                        |
84 +------------------------------------------------------------+
85 1 row in set (0.00 sec)

ADDTIME

 1 ADDTIME(expr1,expr2)
 2 
 3 ADDTIME () añade expr2 a expr1 y devuelve el resultado. 
 4 expr1 es una expresión de tiempo o de fecha y hora, y 
 5 expr2 es una expresión de tiempo.
 6 
 7 MariaDB [Prueba1]> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
 8 +---------------------------------------------------------+
 9 | ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
10 +---------------------------------------------------------+
11 | 2008-01-02 01:01:01.000001                              |
12 +---------------------------------------------------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
16 +-----------------------------------------------+
17 | ADDTIME('01:00:00.999999', '02:00:00.999998') |
18 +-----------------------------------------------+
19 | 03:00:01.999997                               |
20 +-----------------------------------------------+
21 1 row in set (0.00 sec)

CONVERT_TZ

 1 CONVERT_TZ(dt,from_tz,to_tz)
 2 
 3 CONVERT_TZ () convierte un valor datetime dt de la zona de tiempo dado por from_tz 
 4 a la zona horaria dada por to_tz y devuelve el valor resultante. Esta función 
 5 devuelve NULL si los argumentos no son válidos.
 6 
 7 MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
 8 +-----------------------------------------------------+
 9 | CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
10 +-----------------------------------------------------+
11 | 2004-01-01 22:00:00                                 |
12 +-----------------------------------------------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','CDT');
16 +--------------------------------------------------+
17 | CONVERT_TZ('2004-01-01 12:00:00','+00:00','CDT') |
18 +--------------------------------------------------+
19 | NULL                                             |
20 +--------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+CDT');
24 +---------------------------------------------------+
25 | CONVERT_TZ('2004-01-01 12:00:00','+00:00','+CDT') |
26 +---------------------------------------------------+
27 | NULL                                              |
28 +---------------------------------------------------+
29 1 row in set (0.00 sec)
30 
31 MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','CST');
32 +--------------------------------------------------+
33 | CONVERT_TZ('2004-01-01 12:00:00','+00:00','CST') |
34 +--------------------------------------------------+
35 | NULL                                             |
36 +--------------------------------------------------+
37 1 row in set (0.00 sec)
38 
39 MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','-6:00','+4:00');
40 +---------------------------------------------------+
41 | CONVERT_TZ('2004-01-01 12:00:00','-6:00','+4:00') |
42 +---------------------------------------------------+
43 | 2004-01-01 22:00:00                               |
44 +---------------------------------------------------+
45 1 row in set (0.00 sec)

NOW

 1 NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(),
 2 LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()
 3 
 4 Devuelve la fecha y la hora actual como valor en formato 'YYYY-MM-DD HH: MM: SS' 
 5 o el formato YYYYMMDDHHMMSS.uuuuuu, dependiendo de si la función se utiliza en 
 6 una cadena o contexto numérico. El valor se expresa en la zona horaria actual.
 7 
 8 NOW () (o sus sinónimos) se puede usar como el valor predeterminado para columnas 
 9 TIMESTAMP, así como, desde MariaDB 10.0.1, columnas DATETIME. Antes MariaDB 10,0.1, 
10 sólo era posible que una sola columna TIMESTAMP por mesa para contener 
11 la CURRENT_TIMESTAMP como su defecto.
12 
13 LOCALTIME and LOCALTIME() son sinónimos para NOW().
14 LOCALTIMESTAMP and LOCALTIMESTAMP() son sinónimos para NOW().
15 CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() son sinónimos para NOW().
16 
17 MariaDB [(none)]> SELECT NOW();
18 +---------------------+
19 | NOW()               |
20 +---------------------+
21 | 2015-10-27 18:07:00 |
22 +---------------------+
23 1 row in set (0.01 sec)
24 
25 MariaDB [(none)]> SELECT NOW() + 0;
26 +----------------+
27 | NOW() + 0      |
28 +----------------+
29 | 20151027180709 |
30 +----------------+
31 1 row in set (0.02 sec)

CURDATE

 1 CURDATE()
 2 
 3 Devuelve la fecha actual como valor en formato 'YYYY-MM-DD' 
 4 o el formato AAAAMMDD, dependiendo de si la función se utiliza 
 5 en una cadena o contexto numérico.
 6 
 7 CURRENT_DATE y CURRENT_DATE () son sinónimos de CURDATE ().
 8 
 9 MariaDB [Prueba1]> SELECT CURDATE();
10 +------------+
11 | CURDATE()  |
12 +------------+
13 | 2015-10-26 |
14 +------------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT CURDATE() + 50;
18 +----------------+
19 | CURDATE() + 50 |
20 +----------------+
21 |       20151076 |
22 +----------------+
23 1 row in set (0.00 sec)

CURTIME

 1 CURTIME()
 2 
 3 Devuelve la hora actual como un valor en 'HH: MM: SS' o el formato HHMMSS.uuuuuu, 
 4 dependiendo de si la función se utiliza en una cadena o contexto numérico. 
 5 El valor se expresa en la zona horaria actual.
 6 
 7 CURRENT_TIME y CURRENT_TIME () son sinónimos de CURTIME ().
 8 
 9 MariaDB [Prueba1]> SELECT CURTIME();
10 +-----------+
11 | CURTIME() |
12 +-----------+
13 | 23:36:26  |
14 +-----------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT CURTIME() + 55;
18 +----------------+
19 | CURTIME() + 55 |
20 +----------------+
21 |         233693 |
22 +----------------+
23 1 row in set (0.00 sec)

DATE

 1 DATE( expr )
 2 
 3 Extrae la parte de fecha de la fecha o la fecha y hora expresión expr.
 4 
 5 Hasta MariaDB 05/05/32, algunas versiones de MariaDB regresaron 0000-00-00 
 6 cuando se pasa una fecha no válida. Desde 05/05/32, NULL se devuelve.
 7 
 8 MariaDB [Prueba1]> SELECT DATE('2015-10-25 12:21:32');
 9 +-----------------------------+
10 | DATE('2015-10-25 12:21:32') |
11 +-----------------------------+
12 | 2015-10-25                  |
13 +-----------------------------+
14 1 row in set (0.00 sec)

DATEDIFF

 1 DATEDIFF(expr1,expr2)
 2 
 3 DATEDIFF () devuelve (Expr1 - expr2) expresados como un valor en días 
 4 de una fecha a la otra. expr1 y expr2 son de fecha o de expresiones DATE y tiempo 
 5 de expresiones. Sólo las partes de fecha de los valores se utilizan en el cálculo.
 6 
 7 MariaDB [Prueba1]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
 8 +----------------------------------------------+
 9 | DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
10 +----------------------------------------------+
11 |                                            1 |
12 +----------------------------------------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
16 +----------------------------------------------+
17 | DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
18 +----------------------------------------------+
19 |                                          -31 |
20 +----------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 MariaDB [Prueba1]> CREATE TABLE t2 (d DATETIME);
24 Query OK, 0 rows affected (0.23 sec)
25 
26 MariaDB [Prueba1]> INSERT INTO t2 values
27            ("2007-01-30 21:31:07"),
28            ("1983-10-15 06:42:51"),
29            ("2011-04-21 12:34:56"),
30            ("2011-10-30 06:31:41"),
31            ("2011-01-30 14:03:25"),
32            ("2004-10-07 11:19:34");
33 Query OK, 6 rows affected (0.04 sec)
34 Records: 6  Duplicates: 0  Warnings: 0
35 
36 MariaDB [Prueba1]> SELECT NOW();
37 +---------------------+
38 | NOW()               |
39 +---------------------+
40 | 2015-10-26 23:59:32 |
41 +---------------------+
42 1 row in set (0.00 sec)
43 
44 MariaDB [Prueba1]> SELECT d, DATEDIFF(NOW(),d) FROM t2;
45 +---------------------+-------------------+
46 | d                   | DATEDIFF(NOW(),d) |
47 +---------------------+-------------------+
48 | 2007-01-30 21:31:07 |              3191 |
49 | 1983-10-15 06:42:51 |             11699 |
50 | 2011-04-21 12:34:56 |              1649 |
51 | 2011-10-30 06:31:41 |              1457 |
52 | 2011-01-30 14:03:25 |              1730 |
53 | 2004-10-07 11:19:34 |              4036 |
54 +---------------------+-------------------+
55 6 rows in set (0.00 sec)

DATE_FORMAT

 1 DATE_FORMAT(date,format)
 2 
 3 Formatea el valor de DATE de acuerdo a la cadena de formato. 
 4 El lenguaje utilizado para los nombres es controlado por el valor 
 5 de la variable de sistema lc_time_names. Ver la configuración 
 6 regional del servidor para más información sobre los entornos 
 7 locales soportados. Las opciones que se pueden utilizar por 
 8 DATE_FORMAT(), así como su inversa STR_TO_DATE() y el 
 9 
10 MariaDB [Prueba1]> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
11 +------------------------------------------------+
12 | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
13 +------------------------------------------------+
14 | Sunday October 2009                            |
15 +------------------------------------------------+
16 1 row in set (0.00 sec)
17 
18 MariaDB [Prueba1]> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
19 +------------------------------------------------+
20 | DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
21 +------------------------------------------------+
22 | 22:23:00                                       |
23 +------------------------------------------------+
24 1 row in set (0.00 sec)
25 
26 MariaDB [Prueba1]> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
27 +------------------------------------------------------------+
28 | DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
29 +------------------------------------------------------------+
30 | 4th 00 Thu 04 10 Oct 277                                   |
31 +------------------------------------------------------------+
32 1 row in set (0.00 sec)
33 
34 MariaDB [Prueba1]> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
35 +------------------------------------------------------------+
36 | DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
37 +------------------------------------------------------------+
38 | 22 22 10 10:23:00 PM 22:23:00 00 6                         |
39 +------------------------------------------------------------+
40 1 row in set (0.00 sec)
41 
42 MariaDB [Prueba1]> SELECT DATE_FORMAT('1999-01-01', '%X %V');
43 +------------------------------------+
44 | DATE_FORMAT('1999-01-01', '%X %V') |
45 +------------------------------------+
46 | 1998 52                            |
47 +------------------------------------+
48 1 row in set (0.00 sec)
49 
50 MariaDB [Prueba1]> SELECT DATE_FORMAT('2006-06-00', '%d');
51 +---------------------------------+
52 | DATE_FORMAT('2006-06-00', '%d') |
53 +---------------------------------+
54 | 00                              |
55 +---------------------------------+
56 1 row in set (0.00 sec)
57 
58 lc_time_names
59 
60 La configuración regional que determina el idioma utilizado para la fecha 
61 y hora funciones DAYNAME (), MONTHNAME () y DATE_FORMAT (). Nombres Locale 
62 son de idioma y región subetiquetas, por ejemplo 'en_ZA' (Inglés - Sudáfrica) 
63 o 'es_US: Español - Estados Unidos'. El valor por defecto es siempre "en-US",
64 independientemente de la configuración regional del sistema. Ver la 
65 configuración regional del servidor para obtener una lista completa de 
66 los entornos locales soportados. 
67 
68 FROM_UNIXTIME() función, son los siguientes:
Option Description
%D Day with English suffix 'th', 'nd', 'st' or 'rd. (1st, 2nd, 3rd...).
%H Hour with 2 digits between 00-23.
%I Hour with 2 digits between 01-12.
%M Full month name in current locale (Variable lc_time_names).
%S Seconds with 2 digits.
%T Time in 24 hour format. Short for '%H:%i:%S'.
%U Week number (00-53), when first day of the week is Sunday.
%V Week number (01-53), when first day of the week is Sunday. Used with %X.
%W Full weekday name in current locale (Variable lc_time_names).
%X Year with 4 digits when first day of the week is Sunday. Used with %V.
%Y Year with 4 digits.
%a Short weekday name in current locale (Variable lc_time_names).
%b Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c Month with 1 or 2 digits.
%d Day with 2 digits.
%e Day with 1 or 2 digits.
%f Sub seconds 6 digits.
%h Hour with 2 digits between 01-12.
%i Minute with 2 digits
%j Day of the year (001-366)
%k Hour with 1 digits between 0-23.
%l Hour with 1 digits between 1-12.
%m Month with 2 digits.
%p AM/PM according to current locale (Variable lc_time_names).
%r Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%s Seconds with 2 digits.
%u Week number (00-53), when first day of the week is Monday.
%v Week number (01-53), when first day of the week is Monday. Used with %x.
%w Day of the week. 0 = Sunday, 1 = Saturday.
%x Year with 4 digits when first day of the week is Sunday. Used with %v.
%y Year with 2 digits.
# For str_to_date(), skip all numbers.
. For str_to_date(), skip all punctation characters.
@ For str_to_date(), skip all alpha characters.
%% A literal % character.

DATE_SUB

 1 DATE_SUB(date,INTERVAL expr unit)
 2 
 3 Realiza fecha aritmética. El argumento date especifica la fecha o 
 4 la fecha y hora valor inicial. expr es una expresión que especifica 
 5 el valor de intervalo a añadir o restar de la fecha de inicio. 
 6 expr es una cadena; puede comenzar con un "-" para intervalos 
 7 negativos. unit es un operador que indica las unidades en las que 
 8 la expresión debe interpretarse. 
 9 
10 MariaDB [(none)]> SELECT DATE_SUB('2015-10-28', INTERVAL 31 DAY);
11 +-----------------------------------------+
12 | DATE_SUB('2015-10-28', INTERVAL 31 DAY) |
13 +-----------------------------------------+
14 | 2015-09-27                              |
15 +-----------------------------------------+
16 1 row in set (0.06 sec)

DAYNAME

 1 DAYNAME(date)
 2 
 3 Devuelve el nombre del día de la semana de la fecha. El lenguaje utilizado 
 4 para el nombre es controlado por el valor de la variable de sistema
 5 lc_time_names.
 6 
 7 MariaDB [(none)]> SELECT DAYNAME('1951-09-04');
 8 +-----------------------+
 9 | DAYNAME('1951-09-04') |
10 +-----------------------+
11 | Tuesday               |
12 +-----------------------+
13 1 row in set (0.03 sec)
14 
15 MariaDB [(none)]> SELECT DAYNAME('1951-09-04');
16 +-----------------------+
17 | DAYNAME('1951-09-04') |
18 +-----------------------+
19 | Tuesday               |
20 +-----------------------+
21 1 row in set (0.03 sec)
22 
23 MariaDB [(none)]> use Prueba1;
24 Database changed
25 
26 MariaDB [Prueba1]> CREATE TABLE t3 (d DATETIME);
27 Query OK, 0 rows affected (0.48 sec)
28 
29 MariaDB [Prueba1]> INSERT INTO t3 VALUES
30            ("2007-01-30 21:31:07"),
31            ("1983-10-15 06:42:51"),
32            ("2011-04-21 12:34:56"),
33            ("2011-10-30 06:31:41"),
34            ("2011-01-30 14:03:25"),
35            ("2004-10-07 11:19:34");
36 Query OK, 6 rows affected (0.07 sec)
37 Records: 6  Duplicates: 0  Warnings: 0
38 
39 MariaDB [Prueba1]> SET lc_time_names = 'es_MX';
40 Query OK, 0 rows affected (0.02 sec)
41 
42 MariaDB [Prueba1]> SELECT d, DAYNAME(d) FROM t3;
43 +---------------------+------------+
44 | d                   | DAYNAME(d) |
45 +---------------------+------------+
46 | 2007-01-30 21:31:07 | martes     |
47 | 1983-10-15 06:42:51 | sábado     |
48 | 2011-04-21 12:34:56 | jueves     |
49 | 2011-10-30 06:31:41 | domingo    |
50 | 2011-01-30 14:03:25 | domingo    |
51 | 2004-10-07 11:19:34 | jueves     |
52 +---------------------+------------+
53 6 rows in set (0.00 sec)

DAYOFMONTH

 1 DAYOFMONTH(date)
 2 
 3 Devuelve el día del mes para la fecha, en el rango de 1 a 31, 
 4 o 0 para fechas como '0000-00-00' o '2008-00-00', que tiene 
 5 una parte de cero días.
 6 
 7 DAY() es sinónimo para DAYOFMONTH().
 8 
 9 MariaDB [Prueba1]> SELECT DAYOFMONTH('2007-02-03');
10 +--------------------------+
11 | DAYOFMONTH('2007-02-03') |
12 +--------------------------+
13 |                        3 |
14 +--------------------------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> CREATE TABLE t4 (d DATETIME);
18 Query OK, 0 rows affected (0.31 sec)
19 
20 MariaDB [Prueba1]> INSERT INTO t4 values
21     -> ("2007-01-30 21:31:07"),
22     ->     ("1983-10-15 06:42:51"),
23     ->     ("2011-04-21 12:34:56"),
24     ->     ("2011-10-30 06:31:41"),
25     ->     ("2011-01-30 14:03:25"),
26     ->     ("2004-10-07 11:19:34");
27 Query OK, 6 rows affected (0.11 sec)
28 Records: 6  Duplicates: 0  Warnings: 0
29 
30 MariaDB [Prueba1]> SELECT d FROM t4 where DAYOFMONTH(d) = 30;
31 +---------------------+
32 | d                   |
33 +---------------------+
34 | 2007-01-30 21:31:07 |
35 | 2011-10-30 06:31:41 |
36 | 2011-01-30 14:03:25 |
37 +---------------------+
38 3 rows in set (0.00 sec)
39 
40 </syntaxhigh>
41 
42 == DAYOFWEEK ==
43 
44 <syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">
45 
46 DAYOFWEEK(date)
47 
48 Devuelve el indice del día de la semana para la fecha 
49 (1 = Domingo, 2 = Lunes, ..., 7 = Sábado). Estos valores de índice 
50 se corresponden con el estándar ODBC. 
51 Esto contrasta con WEEKDAY() que sigue una numeración índice diferente 
52 (0 = Lunes, 1 = martes ... 6 = domingo).
53 
54 MariaDB [Prueba1]> SELECT DAYOFWEEK('2007-02-03');
55 +-------------------------+
56 | DAYOFWEEK('2007-02-03') |
57 +-------------------------+
58 |                       7 |
59 +-------------------------+
60 1 row in set (0.00 sec)
61 
62 MariaDB [Prueba1]> CREATE TABLE t5 (d DATETIME);
63 Query OK, 0 rows affected (0.39 sec)
64 
65 MariaDB [Prueba1]> INSERT INTO t5 VALUES
66     -> 
67     -> ("2007-01-30 21:31:07"),
68     ->     ("1983-10-15 06:42:51"),
69     ->     ("2011-04-21 12:34:56"),
70     ->     ("2011-10-30 06:31:41"),
71     ->     ("2011-01-30 14:03:25"),
72     ->     ("2004-10-07 11:19:34");
73 Query OK, 6 rows affected (0.06 sec)
74 Records: 6  Duplicates: 0  Warnings: 0
75 
76 MariaDB [Prueba1]> SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) from t5;
77 +---------------------+------------+--------------+------------+
78 | d                   | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
79 +---------------------+------------+--------------+------------+
80 | 2007-01-30 21:31:07 | martes     |            3 |          1 |
81 | 1983-10-15 06:42:51 | sábado     |            7 |          5 |
82 | 2011-04-21 12:34:56 | jueves     |            5 |          3 |
83 | 2011-10-30 06:31:41 | domingo    |            1 |          6 |
84 | 2011-01-30 14:03:25 | domingo    |            1 |          6 |
85 | 2004-10-07 11:19:34 | jueves     |            5 |          3 |
86 +---------------------+------------+--------------+------------+
87 6 rows in set (0.00 sec)

WEEKDAY

 1 WEEKDAY(date)
 2 Devuelve el índice de lunes a viernes por la fecha 
 3 (0 = Lunes, 1 = martes ... 6 = domingo). 
 4 Esto contrasta con DAYOFWEEK() que sigue el estándar ODBC 
 5 (1 = domingo, 2 = Lunes, ..., 7 = sábado).
 6 
 7 MariaDB [Prueba1]> SELECT WEEKDAY('2015-10-30 22:23:00');
 8 +--------------------------------+
 9 | WEEKDAY('2015-10-30 22:23:00') |
10 +--------------------------------+
11 |                              4 |
12 +--------------------------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT WEEKDAY('2015-11-01');
16 +-----------------------+
17 | WEEKDAY('2015-11-01') |
18 +-----------------------+
19 |                     6 |
20 +-----------------------+
21 1 row in set (0.00 sec)
22 
23 MariaDB [Prueba1]> CREATE TABLE t6 (d DATETIME);
24 Query OK, 0 rows affected (0.29 sec)
25 
26 MariaDB [Prueba1]> INSERT INTO t6 VALUES
27     ->  ("2007-01-30 21:31:07"),
28     ->     ("1983-10-15 06:42:51"),
29     ->     ("2011-04-21 12:34:56"),
30     ->     ("2011-10-30 06:31:41"),
31     ->     ("2011-01-30 14:03:25"),
32     ->     ("2004-10-07 11:19:34");
33 Query OK, 6 rows affected (0.11 sec)
34 Records: 6  Duplicates: 0  Warnings: 0
35 
36 MariaDB [Prueba1]> SELECT d FROM t6 where WEEKDAY(d) = 6;
37 +---------------------+
38 | d                   |
39 +---------------------+
40 | 2011-10-30 06:31:41 |
41 | 2011-01-30 14:03:25 |
42 +---------------------+
43 2 rows in set (0.00 sec)

DAYOFYEAR

 1 DAYOFYEAR(date)
 2 
 3 Devuelve el día del año para la fecha, en el rango de 1-366.
 4 
 5 MariaDB [Prueba1]> SELECT DAYOFYEAR('2015-10-30');
 6 +-------------------------+
 7 | DAYOFYEAR('2015-10-30') |
 8 +-------------------------+
 9 |                     303 |
10 +-------------------------+
11 1 row in set (0.00 sec)