Extract – MySQL command to play with DATETIME

Ever wanted to split a datetime into date, time, seconds or hours in MySQL command instead of bringing it into php and processing it? here comes the solution in PHP we have simple date command to do this.

 string date ( string $format [, int $timestamp = time() ] )

Where date() return the current time or the any time given in timestamp into required format such as

date(‘d’) – returns the present date of the month

date(‘n’) – returns the month in numeric

In the same way MySQL also carries a similar command to parse the date into required format.

EXTRACT(unit FROM date)

this  function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

the input for ‘unit’ can be anything of the below.

Unit Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

So in a table ‘order’ where it has a column named ‘OrderDate’ with the order creation date in the format ‘YYYY-mm-dd’.

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1.

The above command will return the year, month and day from the db seperately.