Formatting date values returned from MYSQL table to more readable format including timeYou can check the date_format tutorial to get value directly from mysql without using PHP side formattingWhen we receive date value or date and time value from mysql table, we need to convert them in more readable format based on our requirement. We will use here two PHP functions. The function strtotime() will convert the returned value in mysql date and time format to timestamp. Then we will apply date() function to convert them into our readable format. Here in our example we will keep two fields in the table, one with one DATE field and the other one with DATETIME field. Before this you can read mysql select query on how to get data from a table. <?
This is how the formatting done using strtotime() and date() functions. You can change the date() function formatting based on your requirements. The output of the above script is here. |
| Related Tutorial |
|---|
|
• Date difference • Year from Date field • PHP Date Format |
1,26-10-2004 : 0-00,2005-01-25
2,05-05-2004 : 23-56,2005-06-12
3,08-12-2005 : 13-20,2005-06-06
Here is the sql code to create and fill the table with recordsCREATE TABLE dt_tb (
id int(2) NOT NULL auto_increment,
dt datetime NOT NULL default '0000-00-00 00:00:00',
dt2 date NOT NULL default '0000-00-00',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `dt_tb`
#
INSERT INTO dt_tb VALUES (1, '2004-10-26 00:00:00', '2005-01-25');
INSERT INTO dt_tb VALUES (2, '2004-05-05 23:56:25', '2005-06-12');
INSERT INTO dt_tb VALUES (3, '2005-12-08 13:20:10', '2005-06-06');
This article is written by plus2net.com team.
▼ More on Date & Time functions in SQL
Date & Time functions used in Query for MySQL TableGetting formatted date value from date field in MySQLdate_add function to calculate date & time of MySQL tableFormatting date and time before adding to date field of MySQLString data to Date & time Format by using str_to_dateFormatting string data stored in varchar field to date valueAutomatically updating / inserting current date and time value in a DATETIME fieldRecords of each day by using group by commandCollecting records between two date ranges from MySQL table fieldsGenerating query based on user inputs of date from two calendarsGetting date values from MYSQL table in readable format including timeDifference in days between two date fieldsGetting the year part from date fieldGetting the month part from date fieldGetting the day part from date fieldRecords of today or records of last X seconds by using curdate() or unix_timestampRecords of last one month from today by using date fieldRecords of present week days by using dayofweek function
SQL Video Tutorials