Curso de MariaDB Desde la Consola/Funciones de Fechas y Tiempo
Ir a la navegación
Ir a la búsqueda
- Funciones de Fechas y Tiempo
- Interval
- DATE_ADD ADDDATE
- ADDTIME
- CONVERT_TZ
- NOW
- CURDATE y CURRENT_DATE
- CURTIME y CURRENT_TIME
- DATE
- DATEDIFF
- DATE_FORMAT
- DATE_SUB
- DAYNAME
- DAYOFMONTH
- DAYOFWEEK
- WEEKDAY
- DAYOFYEAR
Funciones de Fechas y Tiempo
Interval
El directivo INTERVAL se puede utilizar para añadir o restar un
intervalo de tiempo a un DATETIME, DATE o TIME valor.
La sintaxis es:
INTERVAL time_quantity Time_unit
MariaDB [(none)]> SELECT '2015-10-21 12:15:30' + INTERVAL 4 day;
+-----------------------------------------+
| '2015-10-21 12:15:30' + INTERVAL 4 day |
+-----------------------------------------+
| 2015-10-25 12:15:30 |
+-----------------------------------------+
1 row in set (0.01 sec)
MariaDB [(none)]> SELECT '2014-05-17 08:44:21' - INTERVAL '7 1' DAY_HOUR;
+-------------------------------------------------+
| '2014-05-17 08:44:21' - INTERVAL '7 1' DAY_HOUR |
+-------------------------------------------------+
| 2014-05-10 07:44:21 |
+-------------------------------------------------+
1 row in set (0.00 sec)
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
Cuando se invoca con la forma INTERVAL del segundo argumento, ADDDATE ()
es sinónimo de DATE_ADD (). Cuando se invoca con la forma DAY del segundo
argumento, MariaDB lo trata como un número entero de días a añadir a expr.
MariaDB [(none)]> SELECT DATE_ADD('2015-10-25', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2015-10-25', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2015-11-25 |
+-----------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT ADDDATE('2015-10-25', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2015-10-25', INTERVAL 31 DAY) |
+----------------------------------------+
| 2015-11-25 |
+----------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT ADDDATE('2015-10-25', 31);
+---------------------------+
| ADDDATE('2015-10-25', 31) |
+---------------------------+
| 2015-11-25 |
+---------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> use Prueba1
Database changed
MariaDB [Prueba1]> CREATE TABLE t1 (d DATETIME);
Query OK, 0 rows affected (0.25 sec)
MariaDB [Prueba1]> INSERT INTO t1 VALUES
-> ("2007-01-30 21:31:07"),
-> ("1983-10-15 06:42:51"),
-> ("2011-04-21 12:34:56"),
-> ("2011-10-30 06:31:41"),
-> ("2011-01-30 14:03:25"),
-> ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [Prueba1]> SELECT d, ADDDATE(d, 10) from t1;
+---------------------+---------------------+
| d | ADDDATE(d, 10) |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
| 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
| 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
| 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
| 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
+---------------------+---------------------+
6 rows in set (0.01 sec)
MariaDB [Prueba1]> SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
+---------------------+------------------------------+
| d | ADDDATE(d, INTERVAL 10 HOUR) |
+---------------------+------------------------------+
| 2007-01-30 21:31:07 | 2007-01-31 07:31:07 |
| 1983-10-15 06:42:51 | 1983-10-15 16:42:51 |
| 2011-04-21 12:34:56 | 2011-04-21 22:34:56 |
| 2011-10-30 06:31:41 | 2011-10-30 16:31:41 |
| 2011-01-30 14:03:25 | 2011-01-31 00:03:25 |
| 2004-10-07 11:19:34 | 2004-10-07 21:19:34 |
+---------------------+------------------------------+
6 rows in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_ADD('2015-12-31 23:59:59', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2015-12-31 23:59:59', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2016-01-01 00:00:00 |
+----------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
+------------------------------------------------------------+
| DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) |
+------------------------------------------------------------+
| 1899-12-30 14:00:00 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
ADDTIME
ADDTIME(expr1,expr2)
ADDTIME () añade expr2 a expr1 y devuelve el resultado.
expr1 es una expresión de tiempo o de fecha y hora, y
expr2 es una expresión de tiempo.
MariaDB [Prueba1]> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997 |
+-----------------------------------------------+
1 row in set (0.00 sec)
CONVERT_TZ
CONVERT_TZ(dt,from_tz,to_tz)
CONVERT_TZ () convierte un valor datetime dt de la zona de tiempo dado por from_tz
a la zona horaria dada por to_tz y devuelve el valor resultante. Esta función
devuelve NULL si los argumentos no son válidos.
MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2004-01-01 22:00:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','CDT');
+--------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','CDT') |
+--------------------------------------------------+
| NULL |
+--------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+CDT');
+---------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+CDT') |
+---------------------------------------------------+
| NULL |
+---------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','CST');
+--------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','CST') |
+--------------------------------------------------+
| NULL |
+--------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','-6:00','+4:00');
+---------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','-6:00','+4:00') |
+---------------------------------------------------+
| 2004-01-01 22:00:00 |
+---------------------------------------------------+
1 row in set (0.00 sec)
NOW
NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(),
LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()
Devuelve la fecha y la hora actual como valor en formato 'YYYY-MM-DD HH: MM: SS'
o el formato YYYYMMDDHHMMSS.uuuuuu, dependiendo de si la función se utiliza en
una cadena o contexto numérico. El valor se expresa en la zona horaria actual.
NOW () (o sus sinónimos) se puede usar como el valor predeterminado para columnas
TIMESTAMP, así como, desde MariaDB 10.0.1, columnas DATETIME. Antes MariaDB 10,0.1,
sólo era posible que una sola columna TIMESTAMP por mesa para contener
la CURRENT_TIMESTAMP como su defecto.
LOCALTIME and LOCALTIME() son sinónimos para NOW().
LOCALTIMESTAMP and LOCALTIMESTAMP() son sinónimos para NOW().
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() son sinónimos para NOW().
MariaDB [(none)]> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2015-10-27 18:07:00 |
+---------------------+
1 row in set (0.01 sec)
MariaDB [(none)]> SELECT NOW() + 0;
+----------------+
| NOW() + 0 |
+----------------+
| 20151027180709 |
+----------------+
1 row in set (0.02 sec)
CURDATE
CURDATE()
Devuelve la fecha actual como valor en formato 'YYYY-MM-DD'
o el formato AAAAMMDD, dependiendo de si la función se utiliza
en una cadena o contexto numérico.
CURRENT_DATE y CURRENT_DATE () son sinónimos de CURDATE ().
MariaDB [Prueba1]> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2015-10-26 |
+------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT CURDATE() + 50;
+----------------+
| CURDATE() + 50 |
+----------------+
| 20151076 |
+----------------+
1 row in set (0.00 sec)
CURTIME
CURTIME()
Devuelve la hora actual como un valor en 'HH: MM: SS' o el formato HHMMSS.uuuuuu,
dependiendo de si la función se utiliza en una cadena o contexto numérico.
El valor se expresa en la zona horaria actual.
CURRENT_TIME y CURRENT_TIME () son sinónimos de CURTIME ().
MariaDB [Prueba1]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 23:36:26 |
+-----------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT CURTIME() + 55;
+----------------+
| CURTIME() + 55 |
+----------------+
| 233693 |
+----------------+
1 row in set (0.00 sec)
DATE
DATE( expr )
Extrae la parte de fecha de la fecha o la fecha y hora expresión expr.
Hasta MariaDB 05/05/32, algunas versiones de MariaDB regresaron 0000-00-00
cuando se pasa una fecha no válida. Desde 05/05/32, NULL se devuelve.
MariaDB [Prueba1]> SELECT DATE('2015-10-25 12:21:32');
+-----------------------------+
| DATE('2015-10-25 12:21:32') |
+-----------------------------+
| 2015-10-25 |
+-----------------------------+
1 row in set (0.00 sec)
DATEDIFF
DATEDIFF(expr1,expr2)
DATEDIFF () devuelve (Expr1 - expr2) expresados como un valor en días
de una fecha a la otra. expr1 y expr2 son de fecha o de expresiones DATE y tiempo
de expresiones. Sólo las partes de fecha de los valores se utilizan en el cálculo.
MariaDB [Prueba1]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
| -31 |
+----------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> CREATE TABLE t2 (d DATETIME);
Query OK, 0 rows affected (0.23 sec)
MariaDB [Prueba1]> INSERT INTO t2 values
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [Prueba1]> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2015-10-26 23:59:32 |
+---------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT d, DATEDIFF(NOW(),d) FROM t2;
+---------------------+-------------------+
| d | DATEDIFF(NOW(),d) |
+---------------------+-------------------+
| 2007-01-30 21:31:07 | 3191 |
| 1983-10-15 06:42:51 | 11699 |
| 2011-04-21 12:34:56 | 1649 |
| 2011-10-30 06:31:41 | 1457 |
| 2011-01-30 14:03:25 | 1730 |
| 2004-10-07 11:19:34 | 4036 |
+---------------------+-------------------+
6 rows in set (0.00 sec)
DATE_FORMAT
DATE_FORMAT(date,format)
Formatea el valor de DATE de acuerdo a la cadena de formato.
El lenguaje utilizado para los nombres es controlado por el valor
de la variable de sistema lc_time_names. Ver la configuración
regional del servidor para más información sobre los entornos
locales soportados. Las opciones que se pueden utilizar por
DATE_FORMAT(), así como su inversa STR_TO_DATE() y el
MariaDB [Prueba1]> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| 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 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52 |
+------------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00 |
+---------------------------------+
1 row in set (0.00 sec)
lc_time_names
La configuración regional que determina el idioma utilizado para la fecha
y hora funciones DAYNAME (), MONTHNAME () y DATE_FORMAT (). Nombres Locale
son de idioma y región subetiquetas, por ejemplo 'en_ZA' (Inglés - Sudáfrica)
o 'es_US: Español - Estados Unidos'. El valor por defecto es siempre "en-US",
independientemente de la configuración regional del sistema. Ver la
configuración regional del servidor para obtener una lista completa de
los entornos locales soportados.
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
DATE_SUB(date,INTERVAL expr unit)
Realiza fecha aritmética. El argumento date especifica la fecha o
la fecha y hora valor inicial. expr es una expresión que especifica
el valor de intervalo a añadir o restar de la fecha de inicio.
expr es una cadena; puede comenzar con un "-" para intervalos
negativos. unit es un operador que indica las unidades en las que
la expresión debe interpretarse.
MariaDB [(none)]> SELECT DATE_SUB('2015-10-28', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('2015-10-28', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2015-09-27 |
+-----------------------------------------+
1 row in set (0.06 sec)
DAYNAME
DAYNAME(date)
Devuelve el nombre del día de la semana de la fecha. El lenguaje utilizado
para el nombre es controlado por el valor de la variable de sistema
lc_time_names.
MariaDB [(none)]> SELECT DAYNAME('1951-09-04');
+-----------------------+
| DAYNAME('1951-09-04') |
+-----------------------+
| Tuesday |
+-----------------------+
1 row in set (0.03 sec)
MariaDB [(none)]> SELECT DAYNAME('1951-09-04');
+-----------------------+
| DAYNAME('1951-09-04') |
+-----------------------+
| Tuesday |
+-----------------------+
1 row in set (0.03 sec)
MariaDB [(none)]> use Prueba1;
Database changed
MariaDB [Prueba1]> CREATE TABLE t3 (d DATETIME);
Query OK, 0 rows affected (0.48 sec)
MariaDB [Prueba1]> INSERT INTO t3 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [Prueba1]> SET lc_time_names = 'es_MX';
Query OK, 0 rows affected (0.02 sec)
MariaDB [Prueba1]> SELECT d, DAYNAME(d) FROM t3;
+---------------------+------------+
| d | DAYNAME(d) |
+---------------------+------------+
| 2007-01-30 21:31:07 | martes |
| 1983-10-15 06:42:51 | sábado |
| 2011-04-21 12:34:56 | jueves |
| 2011-10-30 06:31:41 | domingo |
| 2011-01-30 14:03:25 | domingo |
| 2004-10-07 11:19:34 | jueves |
+---------------------+------------+
6 rows in set (0.00 sec)
DAYOFMONTH
DAYOFMONTH(date)
Devuelve el día del mes para la fecha, en el rango de 1 a 31,
o 0 para fechas como '0000-00-00' o '2008-00-00', que tiene
una parte de cero días.
DAY() es sinónimo para DAYOFMONTH().
MariaDB [Prueba1]> SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
| 3 |
+--------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> CREATE TABLE t4 (d DATETIME);
Query OK, 0 rows affected (0.31 sec)
MariaDB [Prueba1]> INSERT INTO t4 values
-> ("2007-01-30 21:31:07"),
-> ("1983-10-15 06:42:51"),
-> ("2011-04-21 12:34:56"),
-> ("2011-10-30 06:31:41"),
-> ("2011-01-30 14:03:25"),
-> ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.11 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [Prueba1]> SELECT d FROM t4 where DAYOFMONTH(d) = 30;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+
3 rows in set (0.00 sec)
</syntaxhigh>
== DAYOFWEEK ==
<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">
DAYOFWEEK(date)
Devuelve el indice del día de la semana para la fecha
(1 = Domingo, 2 = Lunes, ..., 7 = Sábado). Estos valores de índice
se corresponden con el estándar ODBC.
Esto contrasta con WEEKDAY() que sigue una numeración índice diferente
(0 = Lunes, 1 = martes ... 6 = domingo).
MariaDB [Prueba1]> SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
| 7 |
+-------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> CREATE TABLE t5 (d DATETIME);
Query OK, 0 rows affected (0.39 sec)
MariaDB [Prueba1]> INSERT INTO t5 VALUES
->
-> ("2007-01-30 21:31:07"),
-> ("1983-10-15 06:42:51"),
-> ("2011-04-21 12:34:56"),
-> ("2011-10-30 06:31:41"),
-> ("2011-01-30 14:03:25"),
-> ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [Prueba1]> SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) from t5;
+---------------------+------------+--------------+------------+
| d | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+---------------------+------------+--------------+------------+
| 2007-01-30 21:31:07 | martes | 3 | 1 |
| 1983-10-15 06:42:51 | sábado | 7 | 5 |
| 2011-04-21 12:34:56 | jueves | 5 | 3 |
| 2011-10-30 06:31:41 | domingo | 1 | 6 |
| 2011-01-30 14:03:25 | domingo | 1 | 6 |
| 2004-10-07 11:19:34 | jueves | 5 | 3 |
+---------------------+------------+--------------+------------+
6 rows in set (0.00 sec)
WEEKDAY
WEEKDAY(date)
Devuelve el índice de lunes a viernes por la fecha
(0 = Lunes, 1 = martes ... 6 = domingo).
Esto contrasta con DAYOFWEEK() que sigue el estándar ODBC
(1 = domingo, 2 = Lunes, ..., 7 = sábado).
MariaDB [Prueba1]> SELECT WEEKDAY('2015-10-30 22:23:00');
+--------------------------------+
| WEEKDAY('2015-10-30 22:23:00') |
+--------------------------------+
| 4 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> SELECT WEEKDAY('2015-11-01');
+-----------------------+
| WEEKDAY('2015-11-01') |
+-----------------------+
| 6 |
+-----------------------+
1 row in set (0.00 sec)
MariaDB [Prueba1]> CREATE TABLE t6 (d DATETIME);
Query OK, 0 rows affected (0.29 sec)
MariaDB [Prueba1]> INSERT INTO t6 VALUES
-> ("2007-01-30 21:31:07"),
-> ("1983-10-15 06:42:51"),
-> ("2011-04-21 12:34:56"),
-> ("2011-10-30 06:31:41"),
-> ("2011-01-30 14:03:25"),
-> ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.11 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [Prueba1]> SELECT d FROM t6 where WEEKDAY(d) = 6;
+---------------------+
| d |
+---------------------+
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+
2 rows in set (0.00 sec)
DAYOFYEAR
DAYOFYEAR(date)
Devuelve el día del año para la fecha, en el rango de 1-366.
MariaDB [Prueba1]> SELECT DAYOFYEAR('2015-10-30');
+-------------------------+
| DAYOFYEAR('2015-10-30') |
+-------------------------+
| 303 |
+-------------------------+
1 row in set (0.00 sec)