[Mysql] 날짜 관련 함수 등

mysql에서 날짜와 관련/계산하여 출력하는 각종 예제이다.

 

1. 1993년 1월달의 레코드를 출력해라.

select * from moni6 where year(datetime) = 1993 and month(datetime) = 1

2. 1993년 1월달의 레코드를 뽑아서 O3의 1월 평균값을 구하라.

select avg(03) from moni6 where year(datetime) = 1993 and month(datetime) = 1

3. 1993년 중에서 1월달에 NO값이 4이상인 것들의 레코드를 출력해라..

select * from moni6 where year(datetime) = 1993 and month(datetime) = 1 and no >= 4

4. 1993년부터 2000년까지 1월달의 자료들만 출력해라.

select * from moni6 where datetime >= '1993-01-01' and datetime < '2000-02-01'

5. 1993년부터 2000년까지 일요일의 O3값들만 뽑아서 평균을 구하라.

select avg(o3) from moni6 where datetime >= '1993-01-01' and datetime < '2000-02-01' and weekday(datetime) = 6

6. 1993년부터 2000년까지 월요일의 O3값들만 뽑아서 평균을 구하라.

select avg(o3) from moni6 where datetime >= '1993-01-01' and datetime < '2000-02-01' and weekday(datetime) = 0

7. 1995년 12월의 NO 평균값을 구하라

select avg(NO) from moni6 where year(datetime) = 1995 and month(datetime) = 12

 

위 내용들은 대부분 mysqlhome에서 찾아 응용할 수 있다.

아래는 관련된 mysql 날짜함수들이다.

---

See section 7.3.3 Date and Time Types for a description of the range of values each type has and the valid formats in which date and time values may be specified.

 

TO_DAYS()
Here is an example that uses date functions. The query below selects all records with a date_col value from within the last 30 days:
mysql> SELECT something FROM table
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard:
mysql> select DAYOFWEEK('1998-02-03');
        -> 3

WEEKDAY(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday):
mysql> select WEEKDAY('1997-10-04 22:23:00');
        -> 5
mysql> select WEEKDAY('1997-11-05');
        -> 2

DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31:
mysql> select DAYOFMONTH('1998-02-03');
        -> 3

DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366:
mysql> select DAYOFYEAR('1998-02-03');
        -> 34

MONTH(date)
Returns the month for date, in the range 1 to 12:
mysql> select MONTH('1998-02-03');
        -> 2

DAYNAME(date)
Returns the name of the weekday for date:
mysql> select DAYNAME("1998-02-05");
        -> 'Thursday'

MONTHNAME(date)
Returns the name of the month for date:
mysql> select MONTHNAME("1998-02-05");
        -> 'February'

QUARTER(date)
Returns the quarter of the year for date, in the range 1 to 4:
mysql> select QUARTER('98-04-01');
        -> 2

WEEK(date)
WEEK(date,first)
With a single argument, returns the week for date, in the range 0 to 53 (yes, there may be the beginnings of a week 53), for locations where Sunday is the first day of the week. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday. The week starts on Sunday if the second argument is 0, on Monday if the second argument is 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

YEAR(date)
Returns the year for date, in the range 1000 to 9999:
mysql> select YEAR('98-02-03');
        -> 1998

YEARWEEK(date)
YEARWEEK(date,first)
Returns year and week for a date. The second arguments works exactly like the second argument to WEEK(). Note that the year may be different from the year in the date argument for the first and the last week of the year:
mysql> select YEARWEEK('1987-01-01');
        -> 198653

HOUR(time)
Returns the hour for time, in the range 0 to 23:
mysql> select HOUR('10:05:03');
        -> 10

MINUTE(time)
Returns the minute for time, in the range 0 to 59:
mysql> select MINUTE('98-02-03 10:05:03');
        -> 5

SECOND(time)
Returns the second for time, in the range 0 to 59:
mysql> select SECOND('10:05:03');
        -> 3

PERIOD_ADD(P,N)
Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value:
mysql> select PERIOD_ADD(9801,2);
        -> 199803

PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values:
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)
These functions perform date arithmetic. They are new for MySQL Version 3.22. ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB(). In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() if the expression on the right side is a date or datetime column. (See example) date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or substracted from the starting date. expr is a string; it may start with a `-' for negative intervals. type is a keyword indicating how the expression should be interpreted. The EXTRACT(type FROM date) function returns the 'type' interval from the date. The following table shows how the type and expr arguments are related: type value  Expected expr format  
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 allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise the result is a DATETIME value:
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
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

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like "1:10", MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, "1:10" DAY_SECOND is interpreted in such a way that it is equivalent to "1:10" MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day. Note that if you add or subtract a date value against something that contains a time part, the date value will be automatically converted to a datetime value:
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

If you use really incorrect dates, the result is NULL. If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:
mysql> select DATE_ADD('1998-01-30', Interval 1 month);
        -> 1998-02-28

Note from the preceding example that the word INTERVAL and the type keyword are not case sensitive.

TO_DAYS(date)
Given a date date, returns a daynumber (the number of days since year 0):
mysql> select TO_DAYS(950501);
        -> 728779
mysql> select TO_DAYS('1997-10-07');
        -> 729669

TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calender was changed.

FROM_DAYS(N)
Given a daynumber N, returns a DATE value:
mysql> select FROM_DAYS(729669);
        -> '1997-10-07'

FROM_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calender was changed.

DATE_FORMAT(date,format)
Formats the date value according to the format string. The following specifiers may be used in the format string: %M  Month name (January..December)  
%W  Weekday name (Sunday..Saturday)  
%D  Day of the month with English suffix (1st, 2nd, 3rd, etc.)  
%Y  Year, numeric, 4 digits  
%y  Year, numeric, 2 digits  
%X  Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V'  
%x  Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v'  
%a  Abbreviated weekday name (Sun..Sat)  
%d  Day of the month, numeric (00..31)  
%e  Day of the month, numeric (0..31)  
%m  Month, numeric (01..12)  
%c  Month, numeric (1..12)  
%b  Abbreviated month name (Jan..Dec)  
%j  Day of year (001..366)  
%H  Hour (00..23)  
%k  Hour (0..23)  
%h  Hour (01..12)  
%I  Hour (01..12)  
%l  Hour (1..12)  
%i  Minutes, numeric (00..59)  
%r  Time, 12-hour (hh:mm:ss [AP]M)  
%T  Time, 24-hour (hh:mm:ss)  
%S  Seconds (00..59)  
%s  Seconds (00..59)  
%p  AM or PM  
%w  Day of the week (0=Sunday..6=Saturday)  
%U  Week (0..53), where Sunday is the first day of the week  
%u  Week (0..53), where Monday is the first day of the week  
%V  Week (1..53), where Sunday is the first day of the week. Used with '%X'  
%v  Week (1..53), where Monday is the first day of the week. Used with '%x'  
%%  A literal `%'.  
All other characters are just copied to the result without interpretation:
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'

As of MySQL Version 3.23, the `%' character is required before format specifier characters. In earlier versions of MySQL, `%' was optional.

TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0.

CURDATE()
CURRENT_DATE
Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context:
mysql> select CURDATE();
        -> '1997-12-15'
mysql> select CURDATE() + 0;
        -> 19971215

CURTIME()
CURRENT_TIME
Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:
mysql> select CURTIME();
        -> '23:50:26'
mysql> select CURTIME() + 0;
        -> 235026

NOW()
SYSDATE()
CURRENT_TIMESTAMP
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:
mysql> select NOW();
        -> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
        -> 19971215235026

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time:
mysql> select UNIX_TIMESTAMP();
        -> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580

When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function will receive the value directly, with no implicit ``string-to-unix-timestamp'' conversion. If you give UNIX_TIMESTAMP() a wrong or out-of-range date, it will return 0.

FROM_UNIXTIME(unix_timestamp)
Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:
mysql> select FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)
Returns a string representation of the Unix timestamp, formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function:
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
                            '%Y %D %M %h:%i:%s %x');
        -> '1997 23rd December 03:43:30 x'

SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:
mysql> select SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
        -> 3938

TIME_TO_SEC(time)
Returns the time argument, converted to seconds:
mysql> select TIME_TO_SEC('22:23:00');
        -> 80580
mysql> select TIME_TO_SEC('00:39:38');
        -> 2378