MySQL Date & Time functions
MySQL have several date & time related functions to use in different situations and requirements. We will discuss some of the important functions here and use them for developing some sample codes.
You can read php date & time functions.
| Function | Description |
|---|---|
| BETWEEN | Records Between two dates |
| CURDATE() | Current Date using MYSQL Query |
| DATE_ADD() | Add by specifying INTERVAL in days, month, year , Hour, minutes etc |
| DATE_SUB() | Subtracting Day, month , year , time etc |
| EXTRACT() | Get part of date and time |
| STR_TO_DATE() | Convert String to Date & time Format as required by MySQL date field |
| TO_DAYS() | Difference in days between two Dates |
| DATE_FORMAT() | Formatting Date & Time output as required |
| DAY() | Day part of the date field |
| DAYNAME() | Day Name in Full ( Monday ) |
| DAYOFWEEK() | Weekdays starting from Sunday as 1 and ending with Saturday as 7 |
| DAYOFYEAR() | Number of the day in a Year ( 1 to 366 ) |
| LAST_DAY() | Last Day of the month |
| MONTH() | Month part of the date field |
| MAKEDATE() | Date from Year and DayOfYear |
| MONTHNAME() | Full Month name from date |
| NOW() | Current Date and Time |
| QUARTER() | Quarter of Year Data |
| YEAR() | Year part of the date field |
| YEARWEEK() | Year with Week number of the year |
| WEEK() | Week number in a year (0 - 53) |
| WEEKDAY() | Day Week number in a week (0 - 6) |
| WEEKOFYEAR() | Week number in a year (1 - 53) |
DEMO of Date Functions
From Date of Birth Calculate the retirement date
The person will retire on the last working day of the retirement month.Group by in date field to count records of each day
Getting records of each day by using group by and count command in mysql date fieldData of different quarters of the year
Different Financial Quarter dataFormat to use for creating date string before storing in Date field.
Date fields of MySQL table accept a particular format of Date & time strings, so we have to create such an acceptable format while storing Date values.Collecting date stored as string in varchar field.
We can use str_to_date function to format date value stored as string in a varchar field ( not date field )Adding present date & time automatically to mysql table
We can store the present date & time in a DateTime field while updating adding a record by using default value to CURRENT_TIMESTAMPFormatting returned date value using strtotime function
We can format the returned date value from table by using strtotime function.Generating query
based on user inputs of date from two calendarsToday's records
Practice Questions
- Retrieve the current date.
- Get the current time.
- Calculate the number of days between '2023-06-15' and today's date.
- Display the year of the date '2023-08-03'.
- Calculate the age (in years) of a person born on '1995-05-20'.
- Show the month of the date '2023-08-03'.
- Find the day of the week for '2023-08-03'.
- Format the current date and time as 'August 3, 2023'.
- Add 2 months to the date '2023-08-03'.
- Calculate the time difference in hours between '2023-08-03 10:00:00' and '2023-08-03 14:30:00'.
- Display the current date and time in the 'YYYY-MM-DD HH:MM:SS' format.
- Subtract 5 years and 3 months from the current date.
- Calculate the number of weekdays (non-weekend days) between '2023-08-01' and '2023-08-15'.
- Get the start of the month for the date '2023-08-03'.
- Find the date 2 weeks ago from today.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Connect:
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
| deepak verma | 13-10-2009 |
| a really good work. one of the best sites that gives u exact working answers | |
| noraina norddin | 11-03-2010 |
| how to insert current time in mysql for automatic using php?? | |
| sayed | 24-02-2012 |
| really good | |
SQL Video Tutorials