6.3.4 Функции даты и времени

Описание диапазона величин для каждого типа и возможные форматы представления даты и времени приведены в разделе section 6.2.2 Типы данных даты и времени.

Ниже представлен пример, в котором используются функции даты. Приведенный запрос выбирает все записи с величиной date_col в течение последних 30 дней:

mysql> SELECT something FROM tbl_name
        WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK(date)

Возвращает индекс дня недели для аргумента date (1 = воскресенье, 2 = понедельник, ... 7 = суббота). Эти индексные величины соответствуют стандарту ODBC:

mysql> SELECT DAYOFWEEK('1998-02-03');
        -> 3

WEEKDAY(date)

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

mysql> SELECT WEEKDAY('1997-10-04 22:23:00');
        -> 5

mysql> SELECT WEEKDAY('1997-11-05');
        -> 2

DAYOFMONTH(date)

Возвращает порядковый номер дня месяца для аргумента date в диапазоне от 1 до 31:

mysql> SELECT DAYOFMONTH('1998-02-03');
        -> 3

DAYOFYEAR(date)

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

mysql> SELECT DAYOFYEAR('1998-02-03');
        -> 34

MONTH(date)

Возвращает порядковый номер месяца в году для аргумента date в диапазоне от 1 до 12:

mysql> SELECT MONTH('1998-02-03');
        -> 2

DAYNAME(date)

Возвращает название дня недели для аргумента date:

mysql> SELECT DAYNAME("1998-02-05");
        -> 'Thursday'

MONTHNAME(date)

Возвращает название месяца для аргумента date:

mysql> SELECT MONTHNAME("1998-02-05");
        -> 'February'

QUARTER(date)

Возвращает номер квартала года для аргумента date в диапазоне от 1 до 4:

mysql> SELECT QUARTER('98-04-01');
        -> 2

WEEK(date)

WEEK(date,first)

При наличии одного аргумента возвращает порядковый номер недели в году для date в диапазоне от 0 до 53 (да, возможно начало 53-й недели) для регионов, где воскресенье считается первым днем недели. Форма WEEK() с двумя аргументами позволяет уточнить, с какого дня начинается неделя - с воскресенья или с понедельника. Неделя начинается с воскресенья, если второй аргумент равен 0, и с понедельника - если 1:

mysql> SELECT WEEK('1998-02-20');
        -> 7

mysql> SELECT WEEK('1998-02-20',0);
        -> 7

mysql> SELECT WEEK('1998-02-20',1);
        -> 8

mysql> SELECT WEEK('1998-12-31',1);
        -> 53

Примечание: в версии 4.0 функция WEEK(#,0) была изменена с целью соответствия календарю США.

YEAR(date)

Возвращает год для аргумента date в диапазоне от 1000 до 9999:

mysql> SELECT YEAR('98-02-03');
        -> 1998

YEARWEEK(date)

YEARWEEK(date,first)

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

mysql> SELECT YEARWEEK('1987-01-01');
        -> 198653

HOUR(time)

Возвращает час для аргумента time в диапазоне от 0 до 23:

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

MINUTE(time)

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

mysql> SELECT MINUTE('98-02-03 10:05:03');
        -> 5

SECOND(time)

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

mysql> SELECT SECOND('10:05:03');
        -> 3

PERIOD_ADD(P,N)

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

mysql> SELECT PERIOD_ADD(9801,2);
        -> 199803

PERIOD_DIFF(P1,P2)

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

mysql> SELECT PERIOD_DIFF(9802,199703);
        -> 11

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

ADDDATE(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type)

Данные функции производят арифметические действия над датами. Обе являются нововведением версии MySQL 3.22. Функции ADDDATE() и SUBDATE() - синонимы для DATE_ADD() и DATE_SUB(). В версии MySQL 3.23 вместо функций DATE_ADD() и DATE_SUB() можно использовать операторы + и -, если выражение с правой стороны представляет собой столбец типа DATE или DATETIME (см. пример ниже). Аргумент date является величиной типа DATETIME или DATE, задающей начальную дату. Выражение expr задает величину интервала, который следует добавить к начальной дате или вычесть из начальной даты. Выражение expr представляет собой строку, которая может начинаться с - для отрицательных значений интервалов. Ключевое слово type показывает, каким образом необходимо интерпретировать данное выражение. Вспомогательная функция EXTRACT(type FROM date) возвращает интервал указанного типа (type) из значения даты. В следующей таблице показана взаимосвязь аргументов type и expr:

Значение Type	Ожидаемый формат expr
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
MONTH	MONTHS
YEAR	YEARS
MINUTE_SECOND	"MINUTES:SECONDS"
HOUR_MINUTE	"HOURS:MINUTES"
DAY_HOUR	"DAYS HOURS"
YEAR_MONTH	"YEARS-MONTHS"
HOUR_SECOND	"HOURS:MINUTES:SECONDS"
DAY_MINUTE	"DAYS HOURS:MINUTES"
DAY_SECOND	"DAYS HOURS:MINUTES:SECONDS"

В MySQL формат выражения expr допускает любые разделительные знаки. Разделители, представленные в данной таблице, приведены в качестве примеров. Если аргумент date является величиной типа DATE и предполагаемые вычисления включают в себя только части YEAR, MONTH, и DAY (т.е. не содержат временной части TIME), то результат представляется величиной типа DATE. В других случаях результат представляет собой величину DATETIME:

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
        -> 1998-01-01 00:00:00

mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
        -> 1998-01-01

mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
        -> 1997-12-31 23:59:59

mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND);
        -> 1998-01-01 00:00:00

mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY);
        -> 1998-01-01 23:59:59

mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND);
        -> 1998-01-01 00:01:00

mysql> SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND);
        -> 1997-12-30 22:58:59

mysql> SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);
        -> 1997-12-30 14:00:00

mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
        -> 1997-12-02

Если указанный интервал слишком короткий (т.е. не включает все части интервала, ожидаемые при заданном ключевом слове type), то MySQL предполагает, что опущены крайние слева части интервала. Например, если указан аргумент type в виде DAY_SECOND, то ожидаемое выражение expr должно иметь следующие части: дни, часы, минуты и секунды. Если в этом случае указать значение интервала в виде "1:10", то MySQL предполагает, что опущены дни и часы, а данная величина включает только минуты и секунды. Другими словами, сочетание "1:10" DAY_SECOND интерпретируется как эквивалент "1:10" MINUTE_SECOND. Аналогичным образом в MySQL интерпретируются и значения TIME - скорее как представляющие прошедшее время, чем как время дня. Следует учитывать, что при операциях сложения или вычитания с участием величины DATE и выражения, содержащего временную часть, данная величина DATE будет автоматически конвертироваться в величину типа DATETIME:

mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY);
        -> 1999-01-02

mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);
        -> 1999-01-01 01:00:00

При использовании некорректных значений дат результат будет равен NULL. Если при суммировании MONTH, YEAR_MONTH или YEAR номер дня в результирующей дате превышает максимальное количество дней в новом месяце, то номер дня результирующей даты принимается равным последнему дню нового месяца:

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
        -> 1998-02-28

Из предыдущего примера видно, что слово INTERVAL и ключевое слово type не являются регистро-зависимыми.

EXTRACT(type FROM date)

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

mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
        -> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
        -> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
        -> 20102

TO_DAYS(date)

функция возвращает номер дня для даты, указанной в аргументе date, (количество дней, прошедших с года 0):

mysql> SELECT TO_DAYS(950501);
        -> 728779

mysql> SELECT TO_DAYS('1997-10-07');
        -> 729669

Функция TO_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку не учитывает дни, утерянные при изменении календаря.

FROM_DAYS(N)

Возвращает величину DATE для заданного номера дня N:

mysql> SELECT FROM_DAYS(729669);
        -> '1997-10-07'

Функция FROM_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку она не учитывает дни, утерянные при изменении календаря.

DATE_FORMAT(date,format)

Форматирует величину date в соответствии со строкой format. В строке format могут использоваться следующие определители:

Определитель	Описание
%M	Название месяца (январь...декабрь)
%W	Название дня недели (воскресенье...суббота)
%D	День месяца с английским суффиксом (1st, 2nd, 3rd и т.д.)
%Y	Год, число, 4 разряда
%y	Год, число, 2 разряда
%X	Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с '%V'
%x	Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с '%v'
%a	Сокращенное наименование дня недели (Вс...Сб)
%d	День месяца, число (00..31)
%e	День месяца, число (0..31)
%m	Месяц, число (01..12)
%c	Месяц, число (1..12)
%b	Сокращенное наименование месяца (Янв...Дек)
%j	День года (001..366)
%H	Час (00..23)
%k	Час (0..23)
%h	Час (01..12)
%I	Час (01..12)
%l	Час (1..12)
%i	Минуты, число (00..59)
%r	Время, 12-часовой формат (hh:mm:ss [AP]M)
%T	Время, 24-часовой формат (hh:mm:ss)
%S	Секунды (00..59)
%s	Секунды (00..59)
%p	AM или PM
%w	День недели (0=воскресенье..6=суббота)
%U	Неделя (00..53), где воскресенье считается первым днем недели
%u	Неделя (00..53), где понедельник считается первым днем недели
%V	Неделя (01..53), где воскресенье считается первым днем недели. Используется с `%X'
%v	Неделя (01..53), где понедельник считается первым днем недели. Используется с `%x'
%%	Литерал `%'.

Все другие символы просто копируются в результирующее выражение без интерпретации:

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

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

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

mysql> 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'

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

В MySQL 3.23 символ `%' должен предшествовать символам определителя формата. В более ранних версиях MySQL символ `%' необязателен.

TIME_FORMAT(time,format)

Данная функция используется аналогично описанной выше функции DATE_FORMAT(), но строка format может содержать только те определители формата, которые относятся к часам, минутам и секундам. При указании других определителей будет выдана величина NULL или 0.

CURDATE()

CURRENT_DATE

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

mysql> SELECT CURDATE();
        -> '1997-12-15'

mysql> SELECT CURDATE() + 0;
        -> 19971215

CURTIME()

CURRENT_TIME

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

mysql> SELECT CURTIME();
        -> '23:50:26'

mysql> SELECT CURTIME() + 0;
        -> 235026

NOW()

SYSDATE()

CURRENT_TIMESTAMP

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

mysql> SELECT NOW();
        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;
        -> 19971215235026

UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

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

mysql> SELECT UNIX_TIMESTAMP();
        -> 882226357

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580

При использовании функции UNIX_TIMESTAMP в столбце TIMESTAMP эта функция будет возвращать величину внутренней временной метки непосредственно, без подразумеваемого преобразования строки во временную метку (``string-to-unix-timestamp'' ). Если заданная дата выходит за пределы допустимого диапазона, то функция UNIX_TIMESTAMP() возвратит 0, но следует учитывать, что выполняется только базовая проверка (год 1970-2037, месяц 01-12, день 01-31). Если необходимо выполнить вычитание столбцов UNIX_TIMESTAMP(), результат можно преобразовать к целым числам со знаком. See section 6.3.5 Функции приведения типов.

FROM_UNIXTIME(unix_timestamp)

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

mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'

mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)

Возвращает строковое представление аргумента unix_timestamp, отформатированное в соответствии со строкой format. Строка format может содержать те же определители, которые перечислены в описании для функции DATE_FORMAT():

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
        -> '1997 23rd December 03:43:30 1997'

SEC_TO_TIME(seconds)

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

mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'

mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938

TIME_TO_SEC(time)

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

mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

User Comments

When selecting a timestamp datatype from a table
and want adjust to a timezone (this example is
from pacific time to EST):
SELECT date_format(DATE_ADD([timestampcol],
INTERVAL 3 HOUR), '%M %e, %Y at %H:%i EST') FROM
[db table name];

<div class="comment">Does anyone else notice that
the function
YEARMONTH() is blatantly missing!!! Say you have
two dates and you want to do a period_diff...you
can't just concat() YEAR() and MONTH() because
month is not '0' padded...just seems with
YEARWEEK() it would make sense to also have
YEARMONTH()...thoughts? I know you can do it with
DATE_FORMAT, but then why all the other
functions?</div>

I have to wonder why there is no function that
does basically what this does:

<code>SELECT FLOOR((UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(date_of_birth))/60/60/24/364.25)
as age</code>

It would make a lot of code look a lot
cleaner.

This is even of particular importance for use
on
storing o collecting data about people in the US,
as US law prohibits collecting personal
information about anyone under 13 years of age,
and this trick figures out their age in years.

It would be a lot nicer with an AGE(date
[,date2]
[PERIOD]) function.

The closest thing in there is period_diff,
which
doesn't accept a standard date string and returns
months, which are &lt;sarcasm&gt;oh-so-
useful&lt;/sarcasm&gt;

Actually, this doesn't work in the case of pre-
epoch birthdates, worse yet. unix_timestamp
returns 0 for all pre-epoch dates.

I contend that that is a bug and really needs
to
be fixed.

Have to use period_diff/12 I guess.

You bring up some important issues, but
dealing with ages really isn't <em>that</em>
hard. For
example you could do something like this:

mysql> SELECT DATE_FORMAT(
FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0
AS age FROM people;

Where 'dob' is obviously their date of birth.
It'll also work with pre and post-epoch dates.
Please excuse the funky formatting as the
comment system seems to insist on inserting line
breaks into the code block. I
ran into this problem while working on some
genealogical things over at <a
href='http://www.mullenweg.com'>Mullenweg.com</a>
, a family site. I hope this helps!

<style>
div.comment {
border: 1px dashed black;
padding: 2px;
background-color: #F0F0F0;
}
</style>
<div class="comment">
Seems to be a real pain to get the days in the
month, but here is one way

<code>select
DATE_FORMAT(CONCAT(YEAR('2002-05-05'), '-',
MONTH('2002-05-05' + INTERVAL 1 MONTH), '-01') -
INTERVAL 1 DAY, '%e') as numDays</code>

I guess it would be nice if we could just have a
DATE_FORMAT entity for this</div>

If you're looking for generic SQL queries that will
allow you to get the days, months, and years
between any two given dates, you might consider
using these. You just need to substitute date1 and
date2 with your date fields and mytable with your
table name.


<table border="1">
<tr>
<td>
Number of days between date1 and date2:


SELECT TO_DAYS(date2) -
TO_DAYS(date1) FROM `mytable` WHERE
1</td>
</tr>
</table>


<table border="1">
<tr>
<td>
Number of months between date1 and date2:


SELECT PERIOD_DIFF
(DATE_FORMAT(date2,"%Y%m"),DATE_FORMAT
(date1,"%Y%m")) - (MID(date2, 6, 5) < MID(date1,
6, 5)) FROM `mytable` WHERE 1</td>
</tr>
</table>


<table border="1">
<tr>
<td>
Number of years between date1 and date2:


SELECT (YEAR(date2) - YEAR
(date1)) - (MID(date2, 6, 5) < MID(date1, 6, 5))
FROM `mytable` WHERE 1</td>
</tr>
</table>


Now for some comments about these.


1. These results return integer number of years,
months, and days. They are "floored." Thus, 1.4
days would display as 1 day, and 13.9 years would
display as 13 years.


2. Note that I use boolean expressions in some
cases. Because boolean expressions evaluate to 0
or 1, I can use them to subtract 1 from the total
based on a condition.


For example, to calculate the number of years
between to dates, first simply subtract the years.
The problem is that doing so isn't always correct.
Consider the number of years between July 1, 1950
and May 1, 1952. Technically, there is only one full
year between them. On July 1, 1952 and later,
there will be two years. Therefore, you should
subtract one year in case the date hasn't yet
reached a full year. This is done by checking the if
the second month-day is before the first month-
day. If so, this results in a value of 1, which is
subtracted from the total.


3. To get the month-day, I use MID. This is better
than using RIGHT, since it will work for both dates
and datetimes.


4. As mentioned in a previous post, PERIOD_DIFF
needs yearmonth format, but there is really no best
way to do this. To get this, I use DATE_FORMAT
(date1,"%Y%m").


5. Unlike many other solutions, these queries should
work with dates prior to 01/01/1970.


6. Feedback would be much appreciated. Since I'm
using this in my own applications, I would be happy if
you let me know if you discover an error in the logic.

A couple other time between functions. This is
another way to calculate the months between two
dates which may come in handy at times as it is
linear:
<div style="background-color: #eeeeee; border:
thin dashed black; margin: 5px; padding: 5px;">
Months Between 2002-02-15 and
2002-01-15
(((YEAR('2002-02-15') - 1) * 12 +
MONTH('2002-02-15')) - ((YEAR('2002-01-15') - 1) *
12 + MONTH('2002-01-15'))) - (MID('2002-01-15', 9,
2) < MID('2002-02-15', 9, 2))
</div>
The following is a weeks between function:
<div style="background-color: #eeeeee; border:
thin dashed black; margin: 5px; padding: 5px;">
Weeks Between 2002-08-28 and
2002-08-21
FLOOR((TO_DAYS('2002-08-28') -
TO_DAYS('2002-08-21')) / 7)
</div>

You can't do DATE_ADD("15:30:00", INTERVAL 55
MINUTE), it will return NULL, the workaround I found
is:
DATE_FORMAT(DATE_ADD(CONCAT('2000-01-
01 ',`time_field`),INTERVAL 'minutes' MINUTE), '%
H:%i:%s')

I have two datetime fields, (date_out, date_in) ,
they're records of loggin and logout times and I
need to find the way to get the difference between
the two of them, and I tried this one : f_out -
f_in but it gave me an integer result that is
worthless for me, I need the difference in
seconds Could you please help me , cause I don't
have an idea how to convert this answer to seconds

My user inputs a date in the format
of "MM/DD/YYYYY", how can I convert it to the
format "YYYY/MM/DD" before I save it on a mysql
table?

Hi All,
Adding to my friend Ricky Orea's query, If my user
enters the date in dd mmm yyyy format(26 nov
2002), how should i insert into the mysql db.

Thanks All

%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)

not to be pedantic, but there is never a day "0"
in a month...

How to obtain number of days in a month :

just take the first day of the month, add one
month (to get the first day of the next month) and
substract one day (to get the last day of the
previous month, that is the number of days), as
follow :

select
dayofmonth(date_sub(date_add(concat(date_format(MYDATE,
'%Y-%m'), '-01'), interval 1 month), interval 1
day)) as number_of_days from MYTABLE;

(just replace MYDATE and MYTABLE)

perhaps there's an other way...

I'm wondering why there isn't an easy way to get
the next 5 birthdays out of a birthdaylist. so i always
have to do a 2nd select beginning at the neyt yeas's
first januar...

In order to get the number of seconds between two
datetime values in a table, you could use the
following: SELECT unix_timestamp(date1) -
unix_timestamp(date2) FROM table_name

I need to schedule weekly and monthly event, and my two questions are:
How can I find next Friday's date?
How can I find this month, second Wednesday's date?

Hi there,

I am trying to write a MySQL script that will populate a table with records for each value between 2 given parameters. Without the loop structue available in
Stored Procedures, this is proving problematic. Does anyone have a solution?

Rough Example:-
Table 'test' has a date field 'test_date'.

SELECT @TESTDATE=@START_DATE;
WHILE @TESTDATE <= @END_DATE
INSERT INTO test(test_date) values(@TESTDATE);
SELECT @TESTDATE=@START_DATE + INTERVAL 1 DAYS;
REPEAT

That is the general idea of what I want, not syntactically perfect, but you can see what I mean. I have thought of building a script file with the values I need and then running that, but I would still need to generate all values between the parameters.

Any ideas?

The following will query the dates in a database and give the second wednesday for that date if you set X, and Y to the following:-

X-Day of week to search for (0-monday, 6-Sunday), wednesday=2
Y-Number of weeks forward, from start of month. 1=first week of month, 2=second week of this month.

AvailDate is the field in my database containing a date value, change for your settings. eg change to "now()"

SELECT (AvailDate - INTERVAL (DAYOFMONTH(AvailDate)-1) DAY) + INTERVAL ((Y-1)*7) + ( MOD( ((X+7) - WEEKDAY((AvailDate)-INTERVAL(DAYOFMONTH(AvailDate)-1) DAY)), 7 ) ) DAY AS second_wednesday
FROM database

AND

Next friday can be done using the following, but this gives the same date if the date is a friday. (again X as previous, Friday=4)

SELECT AvailDate, (AvailDate + INTERVAL ( MOD( ((X+7) - WEEKDAY(AvailDate)), 7 ) ) DAY) AS next_friday FROM database

Hope this helps


Next 5 birthdays is easy

SELECT * FROM database
WHERE birthday > now()
ORDER BY birthday
LIMIT 5

ANF the age thing mentioned a few times :-
no-one seems to have fully read the listed functions, or it was added after they posted.(doubtfully)

EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03")
gives 199907 - the required format for PERIOD_DIFF

so age is
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM now()),EXTRACT(YEAR_MONTH FROM the_birth_date))/12

In reference to
"...I have two datetime fields,...loggin and logout times .....need to find the way to get the difference between the two of them..."

At first glance using sec_to_time(time_to_sec(out)-sec_to_time(in)) would work, but does not effectively calculate date. For login time '2002-12-20 23:25:17' and logout '2002-12-21 00:00:31':

select sec_to_time(time_to_sec(log.logout_time)-
time_to_sec(log.login_time)) from log where id=1;

-> -23:24:46 (whoops)

You can clearly see it is subtracting 00:00:31 time from 23:25:17, producing the negative number.

However using the unix timestamp, which gives us the number of seconds from '1970-01-01 00:00:00' for any case, we calculate the difference in seconds and turn it back into a time format:

select sec_to_time(unix_timestamp(log.logout_time)-
unix_timestamp(log.login_time)) from log where id=1;

-> 00:35:14

The result being, 'yippie,' we can go home sometime tonight.

Spent some time trying to work out how to calculate the month start x months ago ( so that I can create historical stats on the fly)

here is what I came up with..

((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1)

this gives you the first day of the month six months before the start of the current month in datetime format

I have a problem with curdate() function ,
when i use select curdate()-35;
it returns invalid date;

does there is some function in mysql by which i can find the date 35 days back or 70 days bak something like that.

Want your users' sessions to expire one hour after the datetime of their last activity? Create an "expiration time" alias from the last activity time using a datetime calculation and later compare it with the current time.

select *,
loginActivityTime + interval 60 minute as loginExpireTime
from loggedIn
where loginSessionID = '$sessionID'";

To get around the need for loops as in my previous posting, I suggest using PHP. This has solved my problem. PHP + MySQL = Prepare to Have Your Socks Blown Off!

Add your own comment.