'Database/MySQL'에 해당되는 글 3건

  1. 2011.04.22 사용자 정의 변수
  2. 2011.04.22 How to simulate FULL OUTER JOIN in MySQL
  3. 2011.04.21 MYSQL 날짜 데이터 타입
Database/MySQL2011. 4. 22. 10:05

사용자 정의 변수

사용자 정의 변수
--------------------------------------------------------------------------------
MySQL에서는  두   가지  방법으로   사용자가  정의한  변수를   지원한다.  변수이름은
alphanumeric 문자와 '_', '$', '.'로 구성된다. 변수에 초기값이 지정되지 않으면, NULL이 디
폴트이며, integer, real, string 값을 저장할 수 있다. 변수이름은 버전 5.0부터는 대·소문자
구분이 없다.

방법1)
SET 문을 사용하여 변수를 설정
SET @variable={integer expression | real expression | string expression }
        [,@variable=...]
【예제】
mysql> set @t3=5;
mysql> select @t3;
+------+
| @t3  |
+------+
| 5    |
+------+
mysql>

방법2)
@variable:=expr 문을 사용하여 설정
【예제】
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
      +----------------------+------+------+------+
      | @t1:=(@t2:=1)+@t3:=4  |  @t1  |  @t2  | @t3  |
      +----------------------+------+------+------+
      |                              5  |   5    |    1    |   4    |
      +----------------------+------+------+------+


'Database > MySQL' 카테고리의 다른 글

How to simulate FULL OUTER JOIN in MySQL  (0) 2011.04.22
MYSQL 날짜 데이터 타입  (0) 2011.04.21
Posted by 아로나
Database/MySQL2011. 4. 22. 10:01

How to simulate FULL OUTER JOIN in MySQL

In this article I’ll show several ways to emulate a FULL OUTER join on a RDBMS that doesn’t support it, as is the case with even the most recent versions of MySQL. This useful query is surprisingly tricky to get right.

Introduction

A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that it includes all rows from both tables, matching them where possible and filling in with NULLs where there is no match. I’ll illustrate that for clarity. Here are two of my favorite tables, apples and oranges:

I’ll join them on price. Here is the left join:

select * from apples as a

    left outer join oranges as o on a.price = o.price

And the right join:

select * from apples as a

    right outer join oranges as o on a.price = o.price

The FULL OUTER JOIN of these two tables, on price, should give the following result:

That’s the result I’ll be working toward in this article. Here is a script to create and populate the example tables, so you can follow along:

create table apples (variety char(10) not null primary key, price int not null);

create table oranges (variety char(10) not null primary key, price int not null);

insert into apples(variety, price) values('Fuji',5),('Gala',6);

insert into oranges(variety, price) values('Valencia',4),('Navel',5);

Method 1: two JOINs and a UNION

One method to simulate a full join is to take the union of two outer joins, for example,

select * from apples as a

    left outer join oranges as o on a.price = o.price

union

select * from apples as a

    right outer join oranges as o on a.price = o.price

This gives the desired results in this case, but it isn’t correct for all cases. Suppose there are duplicate records in the tables (remove the primary key and insert twice to create this situation). UNION eliminates duplicates, which a full join doesn’t do. UNION ALL isn’t the right answer either, because it will cause spurious duplicates. In fact, UNION generates two independent result sets and then combines them, so there is no way to get around this, because the two result sets need to “know about each other” to produce the right results.

There are legitimate cases where duplicate results are expected and correct. For instance, even when the rows are unique, selecting only certain columns, in which there are duplicates, could cause this situation. This doesn’t apply in relational theory, because a set never has duplicates no matter what, but it does in SQL.

Method 2: UNION ALL and an exclusion join

One way to make UNION include only the duplicates I want is to use an exclusion join to eliminate anything from the second result that is already included in the first, like this:

select * from apples as a

   left outer join oranges as o on a.price = o.price

union all

select * from apples as a

   right outer join oranges as o on a.price = o.price

where a.price is null;

This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates.

Method 3: use a mutex table

There’s a case where UNION won’t work: older versions of MySQL don’t support it. All is not lost, though.

I’ve written several articles explaining how to start with a set of mutually exclusive numbers (which I informally call a “mutex table“), then use the mutual exclusivity of the numbers to join things together in interesting ways. This lets me simulate subqueries and unions on earlier versions of MySQL, for example. This approach seems like it might work well here, too. For the following queries I’ll assume my mutex table has the values 0 and 1. Here’s a baseline mutex query for these two tables:

select * from mutex

    left outer join apples as a on i = 0

    left outer join oranges as o on i = 1;

Of course this isn’t a full join. If I want to full join on price, naturally, I need to include price in the join criteria somewhere, and the query above doesn’t even mention the price. But it’s a starting point for tinkering.

The mutex values in the leftmost column, combined with the join criteria, ensure that every row in the two tables gets included on its own row. The mutual exclusivity causes the Navel row not to be matched to the Fuji row, even though they have the same price. The correct behavior of a full join on price is to “fill in” the NULL values where the prices are equal. This modification to the join criteria will fill it in:

select * from mutex

    left outer join apples as a on i = 0

    left outer join oranges as o on i = 1 or a.price = o.price;

The or a.price = o.price relaxes the mutual exclusivity, telling the join to keep the rows separated unless they have the same price:

That’s getting closer. There is a spurious row, though. The Navel row at the bottom of the result set shouldn’t be there; it has already been matched to the Fuji row earlier, so there’s no need to include it with all those NULLs as though there were no matching row in apples. Can I eliminate the Navel row without eliminating the Valencia row?

That turns out to be harder to do. I stared at it for a while, thinking I could include a WHERE clause that would eliminate spurious rows based on the value of i, but after a bit I got a reality check: the row has already been included above, and WHERE clauses work a row at a time, so there’s no way to assert something about one row while applying the WHERE clause to another row. This simple fact is all I needed to realize there’s no way to eliminate the Navel row with the given information.

What I can do, though, is stack another copy of the apples table onto the right-hand side of the results thus far, matching them to the oranges values and confining them to rows with mutex value 1 instead of 0. Now I can write a WHERE clause to see if a row in the i = 1 part of the result set matches a row in the i = 0 part. I’ll write it without the WHERE clause to start:

select * from mutex

   left outer join apples as a on i = 0

   left outer join oranges as o on i = 1 or a.price = o.price

   left outer join apples as a2 on i = 1 and a2.price = o.price;

Now there is a way to tell between the Navel row, which I don’t want, and the Valencia, which I do: the Navel has non-NULL values in the rightmost copy of apples, but the Valencia doesn’t. All I have to do is eliminate rows that have matching values:

select * from mutex

   left outer join apples as a on i = 0

   left outer join oranges as o on i = 1 or a.price = o.price

   left outer join apples as a2 on i = 1 and a2.price = o.price

where o.price is null or a2.price is null

If the WHERE clause is hard to understand, perhaps it’s easier to think of it this way: where not(o.price is not null and a2.price is not null). Both clauses are identical; all I did was apply some boolean identities. Here is the result:

That result has the correct rows, but it has some extra columns, which I don’t need. Here’s the final query:

select a.*, o.* from mutex

   left outer join apples as a on i = 0

   left outer join oranges as o on i = 1 or a.price = o.price

   left outer join apples as a2 on i = 1 and a2.price = o.price

where o.price is null or a2.price is null

Remember, this can’t be done without the mutex table, because I need something to provide non-NULL values for every row; otherwise the joins would not include values where the leftmost table has no rows to contribute.

This technique works if there are duplicate rows, and works on older versions of MySQL, but is probably the least efficient of the three I’ve demonstrated here. As usual, which query is appropriate depends on circumstances.

- reference : http://blueb.net/blog/1081?category=24 -

'Database > MySQL' 카테고리의 다른 글

사용자 정의 변수  (0) 2011.04.22
MYSQL 날짜 데이터 타입  (0) 2011.04.21
Posted by 아로나
Database/MySQL2011. 4. 21. 16:35

MYSQL 날짜 데이터 타입

  • DATE_FORMAT(date,format)

format 스트링에 따라서date 값을 포맷한다.

아래에 나와 있는 지정자 (specifier)들은 format 스트링안에서 사용할 수 있다. ‘%’ 문자는 지정자 문자를 포맷하기 전에 필요한 것이다.

Specifier

Description

%a

Abbreviated weekday name (Sun..Sat)

%b

Abbreviated month name (Jan..Dec)

%c

Month, numeric (0..12)

%D

Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%f

Microseconds (000000..999999)

%H

Hour (00..23)

%h

Hour (01..12)

%I

Hour (01..12)

%i

Minutes, numeric (00..59)

%j

Day of year (001..366)

%k

Hour (0..23)

%l

Hour (1..12)

%M

Month name (January..December)

%m

Month, numeric (00..12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00..53), where Sunday is the first day of the week

%u

Week (00..53), where Monday is the first day of the week

%V

Week (01..53), where Sunday is the first day of the week; used with %X

%v

Week (01..53), where Monday is the first day of the week; used with %x

%W

Weekday name (Sunday..Saturday)

%w

Day of the week (0=Sunday..6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

%%

A literal ‘%’ character

%x

x, for any ‘x’ not listed above

달 및 날짜 지정자를 위한 범위는 0에서부터 시작을 한다..

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> SELECT DATE_FORMAT('2006-06-00', '%d');

        -> '00'

  • DAY(date)

DAY() is a synonym for DAYOFMONTH().

  • DAYNAME(date)

date에 대한 주간 요일 이름을 리턴한다.

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

        -> 'Thursday'

  • DAYOFMONTH(date)

0에서 31 사이의 달별 날짜를 리턴한다.

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

        -> 3

  • DAYOFWEEK(date)

date에 대한 주간 요일 인덱스를 리턴한다 (1 = Sunday, 2 = Monday, …, 7 = Saturday). 이러한 인덱스들은 ODBC 표준을 따른다.

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

        -> 3

  • DAYOFYEAR(date)

1에서 366 사이의 date에 해당하는 일수를 리턴한다.

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

        -> 34

  • EXTRACT(unit FROM date)

EXTRACT() 함수는 DATE_ADD() 또는 DATE_SUB()과 같은 종류의 유닛 지정자를 사용하지만, 날짜 산술식을 실행하는 것이 아닌 날짜에서 부분을 추출하는 기능을 수행한다..

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

mysql> SELECT EXTRACT(MICROSECOND

    ->                FROM '2003-01-02 10:30:00.00123');

        -> 123

  • FROM_DAYS(N)

주어진 날짜 숫자 N에 대해서 DATE 값을 리턴한다.

mysql> SELECT FROM_DAYS(729669);

        -> '1997-10-07'

  • FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

unix_timestamp 인수에 대한 표현식을 이 함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다. unix_timestamp는 UNIX_TIMESTAMP() 함수가 만들어 내는 것과 같은 내부 타임 스탬프 값이다.

만일 format 을 주게 되면, 그 결과는 format 스트링에 따라서 포맷이 되는데, 이것은 DATE_FORMAT() 함수에 대한 엔트리에 목록화 되는 방식과 같은 방식을 사용한다.

mysql> SELECT FROM_UNIXTIME(875996580);

        -> '1997-10-04 22:23:00'

mysql> SELECT FROM_UNIXTIME(875996580) + 0;

        -> 19971004222300

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

    ->                      '%Y %D %M %h:%i:%s %x');

        -> '2003 6th August 06:22:58 2003'

  • GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

포맷 스트링을 리턴한다. 이 함수는 DATE_FORMAT() 및 STR_TO_DATE() 함수를 결합하는데 있어서 매우 유용한 것이다.

첫 번째 인수와 두 번째 인수에 대해서 몇 가지 포맷 스트링 값이 사용 가능하다. 이 값들은 ISO 9075를 참조한다.

Function Call

Result

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

GET_FORMAT(DATETIME,'USA')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'JIS')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'ISO')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'EUR')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'INTERNAL')

'%Y%m%d%H%i%s'

GET_FORMAT(TIME,'USA')

'%h:%i:%s %p'

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

GET_FORMAT(TIME,'EUR')

'%H.%i.%S'

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

TIMESTAMP는 또한 GET_FORMAT() 함수의 첫 번째 인수로 사용이 가능한데, 이와 같은 경우, 함수는 DATETIME 함수와 같은 값을 리턴한다.

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

        -> '03.10.2003'

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));

        -> '2003-10-31'

  • HOUR(time)

time에 대해서 시간 (hour)를 리턴한다. 일별 시간에 대해서는 0에서 23 사이의 값을 가진다. 하지만, TIME 값의 실제 범위는 이것보다 훨씬 크기 때문에, HOUR는 23 보다 큰 값을 리턴할 수 있다.

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

        -> 10

mysql> SELECT HOUR('272:59:59');

        -> 272

  • LAST_DAY(date)

날짜 또는 데이트타임 값을 가져와서 그 달의 가장 마지막 날짜에 해당하는 값을 리턴한다. 만일 인수가 올바르지 않으면 NULL을 리턴한다.

mysql> SELECT LAST_DAY('2003-02-05');

        -> '2003-02-28'

mysql> SELECT LAST_DAY('2004-02-05');

        -> '2004-02-29'

mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

        -> '2004-01-31'

mysql> SELECT LAST_DAY('2003-03-32');

        -> NULL

  • LOCALTIME, LOCALTIME()

LOCALTIME 및 LOCALTIME()는 NOW()과 동일한 것이다.

  • LOCALTIMESTAMP, LOCALTIMESTAMP()

LOCALTIMESTAMP 및 LOCALTIMESTAMP()는 NOW()과 동일하다.

  • MAKEDATE(year,dayofyear)

주어진 연도 및 연도별 날짜 값을 가지고서, 해당하는 날짜를 리턴한다. dayofyear 인수는 0 보다 커야 하며, 그렇지 않을 경우에는 NULL을 리턴한다.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

        -> '2001-01-31', '2001-02-01'

mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);

        -> '2001-12-31', '2004-12-30'

mysql> SELECT MAKEDATE(2001,0);

        -> NULL

  • MAKETIME(hour,minute,second)

hour, minute, 그리고 second 인수를 가지고 계산된 시간 값을 리턴한다.

mysql> SELECT MAKETIME(12,15,30);

        -> '12:15:30'

  • MICROSECOND(expr)

시간 또는 데이트타임 수식 expr 에서 마이크로 세컨드(microsecond) 값을 리턴하는데, 그 범위는0에서 999999 사이가 된다.

mysql> SELECT MICROSECOND('12:00:00.123456');

        -> 123456

mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');

        -> 10

  • MINUTE(time)

time에서 분 (minute)에 해당하는 값을 리턴하는데, 그 범위는 0에서 59 사이가 된다.

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

        -> 5

  • MONTH(date)

date에서 월에 해당하는 값을 리턴하는데, 그 범위는 0에서 12 사이가 된다.

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

        -> 2

  • MONTHNAME(date)

date에 해당하는 월을 전체 이름으로 표시한다.

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

        -> 'February'

  • NOW()

이 함수가 사용되는 문장에 따라서 그 형태를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS로 해서 현재의 날짜 및 시간을 리턴한다.

mysql> SELECT NOW();

        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

        -> 19971215235026

NOW()는 이 명령문이 실행을 시작하는 시점을 나타내는 시간을 상수 값으로 리턴한다. (스토어드 루틴 또는 트리거 안에서는, NOW()는 루틴 또는 트리거링 명령문이 실행되는 시점 값을 리턴한다.) 이 함수는 SYSDATE()와는 차이점을 가지는데, 후자의 경우는 5.0.13 이후에는 함수가 실행된 정확한 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW()               | SLEEP(2) | NOW()               |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

+---------------------+----------+---------------------+

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

+---------------------+----------+---------------------+

이 두 함수간의 차이점에 대해서는 SYSDATE() 함수 설명을 참고로 하기 바란다.

  • PERIOD_ADD(P,N)

N 월 (month)을 기간 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

  • QUARTER(date)

Date에 해당하는 분기를 리턴하는데, 범위는1 에서 4 사이가 된다

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

        -> 2

  • SECOND(time)

time에서 초 부분을 리턴하는데, 범위는 0 에서 59 사이가 된다.

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

        -> 3

  • 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

  • STR_TO_DATE(str,format)

이 함수는 DATE_FORMAT() 함수와는 정 반대 값을 리턴한다. 이 함수는 스트링 str 을 가져와서 스트링 format형태로 포맷을 한다. STR_TO_DATE()는, 포맷 스트링이 날짜 및 시간 부분을 모두 가지고 있는 경우에는 DATETIME 값을, 또는 스트링이 날짜 또는 시간 부분만을 가지고 있는 경우에는 DATE 또는 TIME 값을 리턴한다.

Str에 포함되어 있는 날짜, 시간, 또는 데이트타임 값은 ormat에 의해 지정되는 포맷으로 주어져야 한다. format에서 사용될 수 있는 지정자의 경우는 DATE_FORMAT() 함수 설명을 참조하기 바란다. 만일 str이 올바르지 않은 날짜, 시간, 또는 데이트타임 값을 가지고 있다면, STR_TO_DATE()는 NULL을 리턴한다. MySQL 5.0.3 이후에서는 경고문도 함께 발생한다.

날짜 값의 범위 검사는 Section 11.3.1, “DATETIME, DATE, 그리고 TIMESTAMP 타입”에서 설명을 하고 있다. 예를 들면, 이것은, “제로 (zero)” 날짜 또는 날짜 부분에 0을 가지고 있는 날짜는 SQL 모드가 이러한 값을 허용하지 않도록 설정되지 않는 한 사용 가능하다는 것을 의미한다.

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

        -> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

        -> '2004-04-31'

  • SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

두 번째 인수에 대해서 INTERVAL 폼을 사용해서 호출을 한다면, SUBDATE()는 DATE_SUB()과 동일한 값을 리턴하게 된다.

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

두 번째 형태는 days에 대해서 정수 값을 사용할 수 있도록 하고 있다. 이와 같은 경우, 날짜 또는 데이트타입 수식 expr에서 빼기가 되어야 하는 날짜 수로 해석이 된다.

mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);

        -> '1997-12-02 12:00:00'

Note: "%X%V" 형태를 사용해서 년-주간 스트링을 날짜로 변환 시킬수는 없는데, 그 이유는 연도와 주간의 결합은 해당 주간이 두 개의 달에 걸쳐 있을 경우에는 연도 및 달을 구분할 수 없기 때문이다. 연도-주간 값을 날짜로 변환하기 위해서는, 주간 요일 (weekday)을 함께 지정해 주어야 한다:

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');

        -> '2004-10-18'

  • SUBTIME(expr1,expr2)

SUBTIME() 함수는 expr1 – expr2 수식의 결과 값을 리턴하느데, 그 포맷은 expr1을 따른다.  expr1 은 시간 또는 데이트 타임 수식을 사용할 수 있으며, expr2은 시간 수식이 된다.

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

        -> '1997-12-30 22:58:58.999997'

mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

        -> '-00:59:59.999999'

  • SYSDATE()

함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 현재 날짜 및 시간을 리턴한다.

MySQL 5.0.13 이후부터는, SYSDATE()은 이것이 실행된 시간을 리턴한다. 이 함수는 NOW()과 차이를 가지는데, 후자의 경우는 명령문이 실행을 시작한 상수 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW()               | SLEEP(2) | NOW()               |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

+---------------------+----------+---------------------+

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

+---------------------+----------+---------------------+

또한, SET TIMESTAMP 명령문은 NOW()가 리턴하는 값에는 영향을 주지만, SYSDATE()가 리턴하는 값에는 영향을 주지 않는다. 이것이 의미하는 것은 바이너리 로그에서 설정된 타임 스탬프가 SYSDATE() 함수 호출에는 영향을 주지 않는다는 것을 나타내는 것이다.

SYSDATE()는 동일한 명령문 안에서도 서로 다른 값을 리턴하고, SET TIMESTAMP에 의해서도 영향을 받지 않기 때문에, 이 함수는 논-디터미니스틱 (non-deterministic)이며 따라서 리플리케이션에서는 안전하지가 못하게 된다. 만일 이것이 문제가 된다면, 서버를 --sysdate-is-now 옵션과 함께 구동 시킴으로서 SYSDATE()가 NOW()의 별칭으로 동작하도록 할 수는 있다.

  • TIME(expr)

시간 또는 데이트 타임 수식 expr 에서 시간 부분을 추출하고 그 값을 스트링으로 리턴한다.

mysql> SELECT TIME('2003-12-31 01:02:03');

        -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

        -> '01:02:03.000123'

  • TIMEDIFF(expr1,expr2)

TIMEDIFF()은 expr1 – expr2 수식의 결과를 시간 값으로 리턴한다. expr1 및 expr2 는 시간 또는 날짜-시간 수식이 될 수 있지만, 양쪽 모두 동일한 타입이어야 한다.

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',

    ->                 '2000:01:01 00:00:00.000001');

        -> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',

    ->                 '1997-12-30 01:01:01.000002');

        -> '46:58:57.999999'

  • TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

단일 인수를 사용하게 되면, 이 함수는 날짜 또는 데이트 타임 수식 expr 을 데이트 타임 값으로 리턴한다. 두 개의 인수를 사용하게 되면, 이 함수는 시간 수식 expr2 를 날짜 또는 데이트 타임 수식 expr1 에 추가를 하게 되고 그 결과를 데이트타임 값 형태로 리턴한다.

mysql> SELECT TIMESTAMP('2003-12-31');

        -> '2003-12-31 00:00:00'

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

        -> '2004-01-01 00:00:00'

  • TIMESTAMPADD(unit,interval,datetime_expr)

정수 수식 interval 를 날짜 또는 데이트타임 수식 datetime_expr에 추가를 한다. interval 에 대한 유닛은 unit 인수에 의해 주어지는데, 이 인수는 다음의 값 중에 하나가 되어야 한다: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

unit 값은 위에 나와 있는 키워드중의 하나를 사용하거나 또는 지정될 수 있다. SQL_TSI_의 접두어를 사용해서 지정할 수가 있다. 예를 들면, DAY 및 SQL_TSI_DAY 는 모두 유효하다.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

        -> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

        -> '2003-01-09'

TIMESTAMPADD()는 5.0.0 이후에 사용 가능해졌다.

  • TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

날짜 또는 데이트 타임 수식 datetime_expr1 및 datetime_expr2간의 정수 차이를 리턴한다. 그 결과에 대한 유닛은 unit 인수에 의해 주어진다. unit 에 대한 유효 값은 TIMESTAMPADD() 함수에서 설명된 리스트 값과 같다.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

TIMESTAMPDIFF()는 5.0.0 이후에 사용 가능해졌다.

  • TIME_FORMAT(time,format)

이 함수는 DATE_FORMAT() 함수와 비슷하게 사용되지만, format 스트링은 시간, 분, 그리고 초에만 해당하는 지정자를 가질 수도 있다. 다른 지정자들을 사용하면 NULL 값 또는 0이 나오게 된다.

만일 time 값이 23 보다 큰 시간 부분을 가진다면, %H 및 %k 시간 포맷 지정자는 0에서 23 보다 큰 값을 만들게 된다. 다른 시간 포맷 지정자는 시간 값 모듈로 12를 만든다.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

        -> '100 100 04 04 4'

  • TIME_TO_SEC(time)

time 인수를 초로 변환해서 리턴한다.

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

        -> 80580

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

        -> 2378

  • TO_DAYS(date)

주어진 날짜 date에 대해서, 연도 0에서부터 계산된 날짜 숫자를 리턴한다.

mysql> SELECT TO_DAYS(950501);

        -> 728779

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

        -> 729669

  • UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

만일 아무런 인수를 지정하지 않고 호출을 한다면, 부호를 사용하지 않은 유닉스 타임 스탬프를 리턴한다 ('1970-01-01 00:00:00' UTC 이후의 초). 만일 UNIX_TIMESTAMP()가 date 인수를 사용해서 호출되었다면, 이 함수는 '1970-01-01 00:00:00' UTC 이후의 초 형태로 인수 값을 리턴한다. Date는 DATE 스트링, DATETIME 스트링, TIMESTAMP, 또는 YYMMDD 또는 YYYYMMDD에 있는 숫자가 될 수도 있다. 서버는 date를 현재의 타임 존에 있는 값으로 해석을 해서 UTC의 내부 값으로 변환을 시킨다. 클라이언트는 자신의 타임 존을 Section 5.11.8, “MySQL 서버 타임 존 지원”에서 설명한 방식으로 설정을 한다.

mysql> SELECT UNIX_TIMESTAMP();

        -> 882226357

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

        -> 875996580

UNIX_TIMESTAMP를 TIMESTAMP 컬럼에서 사용하는 경우, 이 함수는 내부 타임 스탬프 값을 직접 리턴하고, 의미적인 (implicit) “스트링에서 유닉스 타임 스탬프로 변환”을 실행하지 않는다. 만일 날짜 범위를 벗어난 값을 UNIX_TIMESTAMP()에 주게 되면, 함수는 0을 리턴한다.

  • UTC_DATE, UTC_DATE()

이 함수가 사용된 문장에 따라서, 현재의 UTC 날짜를 'YYYY-MM-DD' 또는 YYYYMMDD 포맷으로 리턴한다.

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

        -> '2003-08-14', 20030814

  • UTC_TIME, UTC_TIME()

이 함수가 사용된 문장에 따라서, 현재의 UTC 시간을 'HH:MM:SS' 또는 HHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

        -> '18:07:53', 180753

  • UTC_TIMESTAMP, UTC_TIMESTAMP()

이 함수가 사용된 문장에 따라서, 현재의 UTC 날짜를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

        -> '2003-08-14 18:08:04', 20030814180804

  • WEEK(date[,mode])

이 함수는 date에 해당하는 주간 숫자를 리턴한다. WEEK() 함수에 두 개의 인수를 사용하면 해당 주가 일요일 또는 월요일에 시작을 하는지를 지정할 수 있으며 또한 리턴되는 값이 0에서 53 사이 또는 1에서 53 사이에 있는지를 지정할 수가 있게 된다. 만일 mode 인수를 생략한다면, default_week_format 시스템 변수가 사용된다. Section 5.2.2, “서버 시스템 변수”를 참조할 것.

아래의 테이블은 mode 인수가 어떻게 동작을 하는지를 보여주는 것이다.

 

First day

 

 

Mode

of week

Range

Week 1 is the first week …

0

Sunday

0-53

with a Sunday in this year

1

Monday

0-53

with more than 3 days this year

2

Sunday

1-53

with a Sunday in this year

3

Monday

1-53

with more than 3 days this year

4

Sunday

0-53

with more than 3 days this year

5

Monday

0-53

with a Monday in this year

6

Sunday

1-53

with more than 3 days this year

7

Monday

1-53

with a Monday in this year

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

만일 어떤 날짜가 바로 전년도의 마지막 주에 있다면, 여러분이 옵션 인수MySQL은 0을 리턴한다:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

        -> 2000, 0

  • WEEKDAY(date)

Date에 해당하는 주간 요일 인덱스를 리턴한다 (0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');

        -> 1

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

        -> 2

  • WEEKOFYEAR(date)

1에서 53 사이의 달력 주간 숫자를 리턴한다.

mysql> SELECT WEEKOFYEAR('1998-02-20');

        -> 8

  • YEAR(date)

1000에서 9999 사이의 date에 해당하는 연도를 리턴하거나, 또는 “제로” 날짜일 경우에는 0을 리턴한다.

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

        -> 1998

  • YEARWEEK(date), YEARWEEK(date,start)

해당되는 연도 및 주를 리턴한다. start 인수는 WEEK() 함수에서 사용되는 것과 동일하게 동작을 한다. 결과에 나오는 연도는 날짜 인수에 표시되어 있는 연도와 다르게 나올 수도 있다.

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

        -> 198653

- 출처 : http://blog.tini4u.net/entry/MYSQL-%EB%82%A0%EC%A7%9C-%EB%8D%B0%EC%9D%B4%ED%84%B0-%ED%83%80%EC%9E%85 -

'Database > MySQL' 카테고리의 다른 글

사용자 정의 변수  (0) 2011.04.22
How to simulate FULL OUTER JOIN in MySQL  (0) 2011.04.22
Posted by 아로나

티스토리툴바