ユーザ用ツール

サイト用ツール

Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/0/0dd1ea09d2c1a989a6145f9f829cccb7.i failed
Unable to save cache file. Hint: disk full; file permissions; safe_mode setting.
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/0/0dd1ea09d2c1a989a6145f9f829cccb7.metadata failed

mysql時間関数
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/0/0dd1ea09d2c1a989a6145f9f829cccb7.i failed
Unable to save cache file. Hint: disk full; file permissions; safe_mode setting.
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/0/0dd1ea09d2c1a989a6145f9f829cccb7.xhtml failed

実際のサンプル

 SELECT something FROM tbl_name
 WHERE TO_DAYS(date_col) - TO_DAYS(NOW()) <= 5;

今日より6日間後の日付のデータを~ TO_DAYSはPHPで設定した最初からこの指定している日付までの数字をだす。~

 SELECT something FROM tbl_name
 WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

過去30日間内のデータを

SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);

31日後の

SELECT DATE_ADD('1998-01-02', 31);

31日後~ 上記の二つ「ADDDATE」「DATE_ADD」はほぼ同じです。 でも違いがあるようです。

select current_timestamp + interval 3 year

3年後

type の値expr の形式
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
MONTHMONTHS
YEARYEARS
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'
DAY_MICROSECOND'DAYS.MICROSECONDS' ※MySQL 4.1.1~
HOUR_MICROSECOND'HOURS.MICROSECONDS' ※MySQL 4.1.1~
MINUTE_MICROSECOND'MINUTES.MICROSECONDS' ※MySQL 4.1.1~
SECOND_MICROSECOND'SECONDS.MICROSECONDS' ※MySQL 4.1.1~
MICROSECOND'MICROSECONDS' ※MySQL 4.1.1~
SELECT DATE_ADD('2006-12-31 23:59:59', INTERVAL 1 DAY);

2007-01-01 23:59:59 一日後

 DATE_SUB('2007-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);

2007-01-02 10:00:00 一日前、10分

 SELECT DATE_SUB('2007-01-01', INTERVAL 31 DAY);

2006-12-01 31日前

*よく使われる関数 [#yd0102c1]

関数結果説明
select current_date;2003/5/29年月日
select current_time;20:48:35時間のみ
select current_timestamp;2003/5/29 20:48正確な日時
select now();2003/5/29 20:49現在日時
select date_format(now(), '%y.%m.%d');03.05.29現在日時の変換
select date_format(now(), '%Y.%m.%d');2003.05.29 その2
select to_days(now()) - to_days('2003-05-27 00:00:00');2日にちの引き算
date_add(now(), interval 3 day)2003/6/1 21:12日時の作成
select concat(now(), ' test');2003-05-29 21:25:43 test日時にコメント追加
select last_day('2008-02-01');29月の日数
SELECT DAYOFWEEK('2006-12-25');2曜日の表示
SELECT DAYOFMONTH('2006-12-25');25日時の存在チェック
SELECT DAYOFYEAR('2006-12-25');359年間日数のチェック
SELECT YEAR('2006-12-25');2006年の取り出し
SELECT MONTH('2006-12-31 23:50:59');12月の取り出し
SELECT DAYNAME('2006-12-25');Monday曜日のだし
SELECT PERIOD_ADD(0611,1);200612月+
SELECT PERIOD_DIFF(200611, 0711);-12二つ年月の差
SELECT TO_DAYS(20061225); 733035現在までの日数
SELECT FROM_DAYS(733035);2006-12-25日数でその時に年月

*また関連関数 [#q1fd28b7]

NameDescription
ADDDATE()Add time values (intervals) to a date value
ADDTIME()Add time
CONVERT_TZ()Convert from one timezone to another
CURDATE()Return the current date
CURRENT_DATE(), CURRENT_DATESynonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIMESynonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMPSynonyms for NOW()
CURTIME()Return the current time
DATE_ADD()Add time values (intervals) to a date value
DATE_FORMAT()Format date as specified
DATE_SUB()Subtract a time value (interval) from a date
DATE()Extract the date part of a date or datetime expression
DATEDIFF()Subtract two dates
DAY()Synonym for DAYOFMONTH()
DAYNAME()Return the name of the weekday
DAYOFMONTH()Return the day of the month (0-31)
DAYOFWEEK()Return the weekday index of the argument
DAYOFYEAR()Return the day of the year (1-366)
EXTRACT()Extract part of a date
FROM_DAYS()Convert a day number to a date
FROM_UNIXTIME()Format UNIX timestamp as a date
GET_FORMAT()Return a date format string
HOUR()Extract the hour
LAST_DAYReturn the last day of the month for the argument
LOCALTIME(), LOCALTIMESynonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()Synonym for NOW()
MAKEDATE()Create a date from the year and day of year
MAKETIMEMAKETIME()
MICROSECOND()Return the microseconds from argument
MINUTE()Return the minute from the argument
MONTH()Return the month from the date passed
MONTHNAME()Return the name of the month
NOW()Return the current date and time
PERIOD_ADD()Add a period to a year-month
PERIOD_DIFF()Return the number of months between periods
QUARTER()Return the quarter from a date argument
SEC_TO_TIME()Converts seconds to 'HH:MM:SS' format
SECOND()Return the second (0-59)
STR_TO_DATE()Convert a string to a date
SUBDATE()A synonym for DATE_SUB() when invoked with three arguments
SUBTIME()Subtract times
SYSDATE()Return the time at which the function executes
TIME_FORMAT()Format as time
TIME_TO_SEC()Return the argument converted to seconds
TIME()Extract the time portion of the expression passed
TIMEDIFF()Subtract time
TIMESTAMP()With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()Add an interval to a datetime expression
TIMESTAMPDIFF()Subtract an interval from a datetime expression
TO_DAYS()Return the date argument converted to days
TO_SECONDS()Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP()Return a UNIX timestamp
UTC_DATE()Return the current UTC date
UTC_TIME()Return the current UTC time
UTC_TIMESTAMP()Return the current UTC date and time
WEEK()Return the week number
WEEKDAY()Return the weekday index
WEEKOFYEAR()Return the calendar week of the date (0-53)
YEAR()Return the year
YEARWEEK()Return the year and week

*サンプル [#t0efaa9b] その1

SELECT
	Now() as 日付と時間1
	,Year('2010/01/21 1:02:03') as 年1
	,Month('2010/01/21 1:02:03') as 月1
	,Day('2010/01/21 1:02:03') as 日1
	,Hour('2010/01/21 1:02:03') as 時1
	,Minute('2010/01/21 1:02:03') as 分1
	,Second('2010/01/21 1:02:03') as 秒1
	,Now() + 1 as 日数加算1
	,DATE_ADD( Now(), INTERVAL 2 DAY) as 日数加算2
	,DATE_ADD( Now(),  INTERVAL 1 MONTH) as 月数加算 
	,DATE_ADD( Now() , INTERVAL 1 YEAR) as 年数加算 
	,DATE_ADD(Now(),  INTERVAL 1 WEEK) as 週数加算 
limit 1;

その2

SELECT
DATEDIFF( Now(),'2012/03/08') as 経過日数
limit 1;

その3

SELECT
 CURDATE()
 CURDATE() +0
 CURDATE() +1
limit 1;
mysql時間関数.txt · 最終更新: 2013/11/28 11:15 by luis_lee