Collections:
EXTRACT() - Extracting Datetime Component
How to extract components from a given datetime using the EXTRACT() function?
✍: FYIcenter.com
EXTRACT(unit FROM date) is a MySQL built-in function that returns components from a given datetime. For example:
SELECT EXTRACT(YEAR FROM '2023-07-02 10:30:55.000123'); -- +-------------------------------------------------+ -- | EXTRACT(YEAR FROM '2023-07-02 10:30:55.000123') | -- +-------------------------------------------------+ -- | 2023 | -- +-------------------------------------------------+ SELECT EXTRACT(YEAR_MONTH FROM '2023-07-02 10:30:55.000123'); -- +-------------------------------------------------------+ -- | EXTRACT(YEAR_MONTH FROM '2023-07-02 10:30:55.000123') | -- +-------------------------------------------------------+ -- | 202307 | -- +-------------------------------------------------------+ SELECT EXTRACT(DAY_MINUTE FROM '2023-07-02 10:30:55.000123'); -- +-------------------------------------------------------+ -- | EXTRACT(DAY_MINUTE FROM '2023-07-02 10:30:55.000123') | -- +-------------------------------------------------------+ -- | 21030 | -- +-------------------------------------------------------+ SELECT EXTRACT(MICROSECOND FROM '2023-07-02 10:30:55.000123'); -- +--------------------------------------------------------+ -- | EXTRACT(MICROSECOND FROM '2023-07-02 10:30:55.000123') | -- +--------------------------------------------------------+ -- | 123 | -- +--------------------------------------------------------+
Reference information of the EXTRACT() function:
EXTRACT(unit FROM date): int Extracts parts specified by unit keyword from the date. Arguments, return value and availability: unit: Required. Components to extract as datetime internal unit keyword. date: Required. The date to extract components from. int: Return value. Extracted components concatenated as an integer. Available since MySQL 4. Datetime interval unit keywords: Unit keyword Output integer format ------------------ --------------------- MICROSECOND ffffff SECOND ss MINUTE ii HOUR HH DAY dd WEEK VV MONTH mm QUARTER q YEAR YYYY SECOND_MICROSECOND ssffffff MINUTE_MICROSECOND iissffffff MINUTE_SECOND iiss HOUR_MICROSECOND HHiissffffff HOUR_SECOND HHiiss HOUR_MINUTE HHii DAY_MICROSECOND ddHHiissffffff DAY_SECOND ddHHiiss DAY_MINUTE ddHHii DAY_HOUR ddHH YEAR_MONTH YYYYmm
⇒ FORMAT_PICO_TIME() - Formatting Picoseconds in Readable Units
2023-11-17, 250🔥, 0💬
Popular Posts:
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...