Timestamp Functions

The Timestamp functions are divided into the categories specified below:

Current date/time

NOW() 
now() - Returns current server date and time as timestamp.

CURDATE() 
curdate() - Returns current server date as timestamp.

Date/time parts

YEAR()

year(ts) - Extracts the year portion from ts.

Example: 
year('2014-02-25 13:14:15')→2014

QUARTER()
quarter(ts) - Extracts the quarter portion from ts.

Example:
quarter('2014-02-25 13:14:15') -> 1

MONTH()
month(ts) - Extracts the month portion from ts.

Example:
month('2014-02-25 13:14:15') -> 2

WEEKOFYEAR()
weekofyear(ts) - Extracts the week number from the beginning of year of ts.

Example:
weekofyear('2014-02-25 13:14:15') -> 9

DAY() / DAYOFMONTH()
day(ts) - Extracts the day portion from ts.

Example: 
day('2014-02-25 13:14:15') -> 25

The function dayofmonth() is an alias for the function day().

DAYOFWEEK()
dayofweek(ts) - Extracts day of week of ts as number.

Example:
dayofweek('2014-02-25 13:14:15') -> 3

HOUR()
hour(ts) - Extracts the hour portion from ts as number.

Example:
hour('2014-02-25 13:14:15') -> 13

MINUTE()
minute(ts) - Extracts the minutes portion from ts as number.

Example: 
minute('2014-02-25 13:14:15') -> 14

SECOND()
second(ts) - Extracts the seconds portion from ts as number.

Example: 
second('2014-02-25 13:14:15') -> 15

MICROSECOND()
microsecond(ts) - Extracts the microsecond portion from ts as number.

Example:
microsecond('2014-02-25 13:14:15.250') -> 250000

Date/time Manipulation and Conversion

DATEDIFF() 

datediff(ts1, ts2) - Calculates the difference in days (ts1-ts2). Returns integer.

Example: 
datediff('2014-02-25','2014-02-22') -> 3

DATE_ADD() 
date_add(ts, i) - Adds i days to ts. Returns timestamp.

Example: 
date_add('2014-02-25 13:14:15', 2) -> '2014-02-27 13:14:15'

DATE_SUB() 
date_sub(ts, i) - Subtracts i days from ts. Returns timestamp.

Example: 
date_sub('2014-02-25 13:14:15', 2) -> '2014-02-23 13:14:15'

TIMESTAMPADD()
timestampadd(unit, i, ts) - Adds interval i to ts. Returns timestamp.

The unit for interval is given by unit. The interval units are: 
year / quarter / month / week / day / hour / minute / second / microsecond / frac_second. 
Unit may include SQL_TSI_ prefix.

Examples:
timestampadd(MINUTE,1,'2010-01-02') -> '2010-01-02 00:01:00'
timestampadd(SQL_TSI_YEAR,1,'2010-10-02') -> '2011-10-02'

TIMESTAMPDIFF() 
timestampdiff(unit,ts1, ts2) - Calculates ts1-ts2. Returns integer.

The unit for the difference interval is given by unit. Interval units are:
year / quarter / month / week / day / hour / minute / second / microsecond / frac_second. 
Unit may include SQL_TSI_ prefix.

Examples:
timestampdiff(MONTH,'2010-02-01','2010-04-01') -> 2
timestampdiff(SQL_TSI_YEAR,'2010-10-02','2014-10-02') -> '4'

TRUNC()
trunc(ts, unit) - Truncates ts to date/time element level unit. Returns timestamp.

The Unit date/time elements are:
year / quarter / month / week / day / hour / minute / second

Example:
trunc('2014-02-25 13:14:15','month') -> '2014-02-01 00:00:00'

TO_TIMESTAMP() 
to_timestamp(s) - Explicitly converts string s to timestamp. The format of the converted string s is: 'yyyy-MM-dd HH:mm:ss.SSS...'

Example: 
to_timestamp('2014-02-25 13:14:15') -> {ts}

UNIX_TIMESTAMP()

unix_timestamp() - Returns current server time as number of seconds since the epoch: 1970/1/1.

unix_timestamp(ts) - Converts timestamp ts to integer values representing the number of seconds since the epoch: 1970/1/1.

Example: 
unix_timestamp('2014-02-25 13:14:15') -> 1393334055