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