๐Ÿ—“[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] DATETIME์—์„œ DATE๋กœ ํ˜• ๋ณ€ํ™˜

Chobbyยท2022๋…„ 4์›” 4์ผ
0

SQL

๋ชฉ๋ก ๋ณด๊ธฐ
14/41

๋ฌธ์ œ ์„ค๋ช…

ANIMAL_INS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_INS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””, ์ƒ๋ฌผ ์ข…, ๋ณดํ˜ธ ์‹œ์ž‘์ผ, ๋ณดํ˜ธ ์‹œ์ž‘ ์‹œ ์ƒํƒœ, ์ด๋ฆ„, ์„ฑ๋ณ„ ๋ฐ ์ค‘์„ฑํ™” ์—ฌ๋ถ€๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

NAMETYPENULLABLE
ANIMAL_IDVARCHAR(N)FALSE
ANIMAL_TYPEVARCHAR(N)FALSE
DATETIMEDATETIMEFALSE
INTAKE_CONDITIONVARCHAR(N)FALSE
NAMEVARCHAR(N)TRUE
SEX_UPON_INTAKEVARCHAR(N)FALSE

ANIMAL_INS ํ…Œ์ด๋ธ”์— ๋“ฑ๋ก๋œ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ์— ๋Œ€ํ•ด, ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””์™€ ์ด๋ฆ„, ๋“ค์–ด์˜จ ๋‚ ์งœ๋ฅผ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ์•„์ด๋”” ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

์˜ˆ์‹œ

์˜ˆ๋ฅผ ๋“ค์–ด, ANIMAL_INS ํ…Œ์ด๋ธ”์ด ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค๋ฉด

ANIMAL_INS

ANIMAL_IDANIMAL_TYPEDATETIMEINTAKE_CONDITIONNAMESEX_UPON_INTAKE
A349996Cat2018-01-22 14:32:00NormalSugarNeutered Male
A350276Cat2017-08-13 13:50:00NormalJewelSpayed Female
A350375Cat2017-03-06 15:01:00NormalMeoNeutered Male
A352555Dog2014-08-08 04:20:00NormalHarleySpayed Female
A352713Cat2017-04-13 16:29:00NormalGiaSpayed Female

SQL๋ฌธ์„ ์‹คํ–‰ํ•˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™์ด ๋‚˜์™€์•ผ ํ•ฉ๋‹ˆ๋‹ค.

ANIMAL_IDNAME๋‚ ์งœ
A349996Sugar2018-01-22
A350276Jewel2017-08-13
A350375Meo2017-03-06
A352555Harley2014-08-08
A352713Gia2017-04-13

๋ณธ ๋ฌธ์ œ๋Š” Kaggle์˜ "Austin Animal Center Shelter Intakes and Outcomes"์—์„œ ์ œ๊ณตํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์‚ฌ์šฉํ•˜์˜€์œผ๋ฉฐ ODbL์˜ ์ ์šฉ์„ ๋ฐ›์Šต๋‹ˆ๋‹ค.


  1. ์‹œ๊ฐ(์‹œ-๋ถ„-์ดˆ)์„ ์ œ์™ธํ•œ ๋‚ ์งœ(๋…„-์›”-์ผ)๋งŒ ๋ณด์—ฌ์ฃผ์„ธ์š”. โ†ฉ

๋‚˜์˜ ํ’€์ด

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') AS '๋‚ ์งœ' 
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

DATE_FORMAT ๋‚ด์žฅ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋‚ ์งœ๋ฅผ ์›ํ•˜๋Š” ํ˜•์‹์œผ๋กœ ๋ณ€ํ™˜

profile
๋‚ด ์ง€์‹์„ ๊ณต์œ ํ•  ์ˆ˜ ์žˆ๋Š” ๋Œ€๋‹ดํ•จ

0๊ฐœ์˜ ๋Œ“๊ธ€