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에서 발취한 내용입니다. 직접 방문해 보시는 것도 좋을듯…
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.
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