| | |
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.
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.
We can collect data from date field by using SELECT query and then use date_format to collect data in our required way.
Getting records of each day by using group by and count command in mysql 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.
Before adding Date data we can use str_to_date() function to covert our formatted string data to MySQL accepted date format.
We can use str_to_date function to format date value stored as string in a varchar field ( not date field )
We can store the present date & time in a DateTime field while updating adding a record by using default value to CURRENT_TIMESTAMP
We can use BETWEEN command to collect records of two date ranges.
We can format the returned date value from table by using strtotime function.
We can use to_days function to find out the difference between two date values of mysql table.
We can collect only year part from a date & time value stored in a mysql table by using year() function.
From the value of date & time of Mysql table we can get month part by using month function.
We can separate out day part from MySQL table and apply math on it to get the different day calculations.
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.
To collect the records of this week starting from Monday till today we have to use dayofweek function.
| | 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 |
|
|
|
|
|
|
