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 |
YEAR | Years |
SECOND_MICROSECOND | Seconds.Microseconds |
MINUTE_MICROSECOND | Minutes.Seconds.Microseconds |
MINUTE_SECOND | Minutes.Seconds |
HOUR_MICROSECOND | Hours.Minutes.Seconds.Microseconds |
HOUR_SECOND | Hours.Minutes.Seconds |
HOUR_MINUTE | Hours.Minutes |
DAY_MICROSECOND | Days Hours.Minutes.Seconds.Microseconds |
DAY_SECOND | Days Hours.Minutes.Seconds |
DAY_MINUTE | Days Hours.Minutes |
DAY_HOUR | Days Hours |
YEAR_MONTH | Years-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)
