MySQL Date & Time functions

There is exclusive fields type to store Date and Date –Time in mysql or in other databases. As we know in any script date plays important role and we need to do many calculation and formatting to handle the date issues. It can be a simple Library book issue management to a customer compliant handling, these date fields are important for handling related data in a table.

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.

date_add to add date & time to field data

We can use date_add function to calculate date & time values and store them in MySql table. Future dates can be updated by adding time interval to current date.

Formatting Date field data of mysql table by date_format

We can collect data from date field by using SELECT query and then use date_format to collect data in our required way.

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 field

Format 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.

str_to_date() function to convert string data to date format for mysql table

Before adding Date data we can use str_to_date() function to covert our formatted string data to MySQL accepted date format.

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_TIMESTAMP

Collecting record between two date periods

We can use BETWEEN command to collect records of two date ranges.

Formatting returned date value using strtotime function

We can format the returned date value from table by using strtotime function.

to_days function to get the difference in days between to date and time values

We can use to_days function to find out the difference between two date values of mysql table.

Getting year part from date field

We can collect only year part from a date & time value stored in a mysql table by using year() function.

Getting month part form a date field of mysql table

From the value of date & time of Mysql table we can get month part by using month function.

Getting day part from table

We can separate out day part from MySQL table and apply math on it to get the different day calculations.

Collecting records of last week or last 15 days

By specifying days we can collect records of last 7 days or 15 days or any X days from today by using DATE_SUB function.

Dayofweek function

To collect the records of this week starting from Monday till today we have to use dayofweek function.

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Your Rating


deepak verma


a really good work. one of the best sites that gives u exact working answers
noraina norddin


how to insert current time in mysql for automatic using php??


really good

Post Comment This is for short comments only. Use the forum for more discussions.

Subscribe to our mailing list

* indicates required
Subscribe to plus2net

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2016 All rights reserved worldwide Privacy Policy Disclaimer