날짜&시간 데이터 다루기

목차

• 날짜&시간
  • 비교연산자
  • 날짜 포맷 변경하기
  • 시간 더하고 빼기
  • DATETIME 분리하기
• 예제
• 출처

날짜&시간

• MySQL의 날짜&시간 Data Type
  • 
  • DATETIME에서 DATETIME(6) 처럼 숫자를 기입하게 되면 소수점 6자리인 0.000000초까지 저장해줍니다. 
    • 최대 6까지만 입력이 가능합니다.
• 날짜 & 시간 데이터를 입력할때는 문자처럼 기입하면 됩니다.
  • DATE : 연-월-일 형태 
    • DATE 입력 예시

      • INSERT INTO newtable (날짜2) VALUES ("2024-01-01")

  • DATETIME : 연-월-일 시:분:초 형태
    • DATATIME 입력 예시 

      • INSERT INTO newtable (날짜) VALUES ("2024-01-01 12:05:00")

비교연산자

•  비교연산자
  • 
  • 등호, 부등호를 사용해서 원하는 DATETIME 자료를 가져올 수 있습니다.
    

    • SELECT * FROM blog WHERE 발행일 > '2022-03-10 08:24:25'

      • 

    • SELECT * FROM blog WHERE 발행일 = '2022-03-10 08:24:25'

      
      
      • 
  • 특정 날짜의 데이터 가져오기

    • SELECT * FROM blog 
      WHERE 발행일 >= '2022-03-10 00:00:00' 
        AND 발행일 < '2022-03-11 00:00:00'

      
      
      • 
    • BETWEEN은 왼쪽 오른쪽 값을 모두 포함하는 이상,이하이기때문에 2022-03-11 00:00:00 데이터가 포함될 수 있습니다.
      • 만약 이를 방지하기 위해 다음과 같이 BETWEEN '2022-03-10 00:00:00' AND '2022-03-10 23:59:59' 같이 지정해도 되지만, MySQL은 시간을 ms 단위까지 저장하기 때문에 2022-03-10 23:59:59.529 초 같은 데이터가 빵구날 수가있습니다.
    • 아래처럼 date를 통해서 비교할수도 있는데 (date()안에 데이터를 넣으면 DATETIME -> DATE 형식으로 바꿔줍니다.) 이렇게 할 경우 index를 못 쓰는 방법이라 느릴 수 있습니다.

      • SELECT * FROM blog WHERE date(발행일) = '2022-03-10'

  • 특정날짜부터 현재시간까지 발행된 글들만 가져오기

    • SELECT * FROM blog 
      WHERE 발행일 > '2022-03-10 00:00:00' 
        AND 발행일 <= now()

      • now() 안에는 datetime(6)처럼 초단위 소수점 몇째짜리까지 알려주게 됩니다.
    • 아래처럼 하게 될면 안될까요? -> 데이터베이스에는 미래 시간이 기록될 수도 있기때문에 항상 정확하게 조건을 주는 것이 좋습니다.

      • SELECT * FROM blog 
        WHERE 발행일 > '2022-03-10 00:00:00'

날짜 포맷 변경하기

• DATETIME에 있는 년,월,일,시,분,초 정보를 이용하여 varchar로 변경해서 출력해줄 수 있습니다.
  • %Y : 년도
  • %m : 월
  • %d : 일
  • %H : 시
  • %i : 분
  • %s : 초
• 예시

  • SELECT date_format(now(), '현재는 %Y년도 %m월 %d일 %H시 %i분 %s초 입니다.')

    • 

시간 더하고 빼기

• INTERVAL 이용하기
  • INTERVAL 기간
    
    • YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
    • YEAR_MONTH, DAY_SECOND
  • 예시
    • NOW() + INTERVAL 1 YEAR
      • 1년후 날짜 출력
    • NOW() - INTERVAL 1 MONTH
      • 1달전 날짜 출력
    • NOW() + INTERVAL 3 DAY
      • 3일후 날짜 출력
    • NOW() + INTERVAL "1-2" YEAR_MONTH + INTERVAL "5 1:10:30" DAY_SECOND
      • 현재시간으로부터 1년 2개월 5일 1시간 10분 30초 뒤의 날짜를 출력해줍니다.

DATETIME 분리하기

• DATETIME은 다음 함수들을 이용하여 년도, 월, 일, 시, 분, 초을 분리할 수 있습니다.
  • YEAR() , MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
  • 예시
    • YEAR (NOW()) 
    • SECOND(NOW())

예제

• login_record.csv
  • id, first_name, email, last_login 정보가 있는 테이블 (어떤 사이트의 유저의 이름, 이메일, 9월부터 11월까지의 마지막 접속시간(DATETIME)이 저장된 테이블)
• 2022년 11월에 최소 1회 접속한 유저의 수를 구해보기

  • SELECT COUNT(*) FROM login_record 
    WHERE last_login>="2022-11-01 00:00:00" AND last_login<"2022-12-01 00:00:00"

• 현재 테이블의 모든 날짜 시간에 1년을 더해서 출력하기

  • SELECT last_login + INTERVAL 1 YEAR FROM login_record;

• 9월의 짝수일에 해당하는 행만 출력하기

  • SELECT * FROM login_record 
    WHERE MONTH(last_login)=9 AND DAY(last_login)%2=0

출처

• codding apple [한 번에 끝내는 SQL & Database]