Jump to content


Photo

Reformat "datetime" brought from mysql db


  • Please log in to reply
4 replies to this topic

#1 xtheonex

xtheonex
  • New Members
  • Pip
  • Newbie
  • 8 posts
  • LocationBurton-Upon-Trent, Staffordshire, UK

Posted 26 March 2006 - 04:41 AM

Hi guys.
This is probably really simple but i started learning php/mysql about two years ago. Had a "break" for a while and now im coming back to it with a couple of projects i need a little help if you can spare the time.

I have a mysql database with a "datetime" colum.
When i fetch the string from the database it comes in an "unfriendly format. I'd Like to be able to firstly, split the time and date into separate strings. And also reverse the date format.
The format it is fetched from, and stored in the database is yyyy-mm-dd hh:mm:ss however on fetching it from the database i'd like to be able to split the date and time, and reverse the date format so its dd-mm-yyyy.
If anyone can spare some time to explain how to do it id be very grateful.

Thanks in advance,
Dave

#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 26 March 2006 - 04:48 AM

Use the MySQL date_format() function. See:

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#id3201971\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/dat....html#id3201971[/a]



#3 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 26 March 2006 - 04:53 AM

from the manual good luck.



My function (http://phpshortcuts.blog-city.com/dateconvert_1.htm 
) for converting system dates into user-friendly dates depending on the scenario in which it's used: 

<? 
function DateConvert($old_date, $layout) 
{ 
//Remove non-numeric characters that might exist (e.g. hyphens and colons) 
$old_date = ereg_replace('[^0-9]', '', $old_date); 

//Extract the different elements that make up the date and time 
$_year = substr($old_date,0,4); 
$_month = substr($old_date,4,2); 
$_day = substr($old_date,6,2); 
$_hour = substr($old_date,8,2); 
$_minute = substr($old_date,10,2); 
$_second = substr($old_date,12,2); 

//Combine the date function with mktime to produce a user-friendly date & time 
$new_date = date($layout, mktime($_hour, $_minute, $_second, $_month, $_day, $_year)); 
return $new_date; 
} 
?> 

The function is then called like this: 

<? 
$todays_date = "2005-07-01 12:35:01"; 

//We can use words to desccribe the date (as you see on on my blog) 
print DateConvert($todays_date, "l, jS F Y");//Friday, 1st July 2005 

//We can use the UK style 
print DateConvert($todays_date, "d/m/Y");//01/07/2005 

//We can use the US style 
print DateConvert($todays_date, "Y/m/d");//2005/07/01 

//We can even add the time 
print DateConvert($todays_date, "Y/m/d - g:ia");//2005/07/01 12:35pm 
?> 

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#4 xtheonex

xtheonex
  • New Members
  • Pip
  • Newbie
  • 8 posts
  • LocationBurton-Upon-Trent, Staffordshire, UK

Posted 26 March 2006 - 05:05 AM

Thanks guys.

toplay, unfortunatley, im not very well versed with manual mysql type stuff. I usually use PHPMyAdmin to administer my databases. Its a cheats way i know but it works just fine for me at the moment. I will learn to use mysql manually one day but im concentrating more on using it with php to make scripts and such like.

redarrow. The function works perfectly and is exactly what i was looking for. Cheers :D

#5 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 26 March 2006 - 05:18 AM

I'm not sure what you mean by "manual", and then say you're learning to use SQL with PHP. Do whatever you wish.

I personally find the below SQL example is easier to retrieve date and time than that PHP function posted. If SQL has the functions to do something, it's best to utilize it instead of making PHP do it.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DATE_FORMAT(date_column, '%d[span style=\'color:orange\']-%m-%Y'[/span]) AS formatted_date, DATE_FORMAT(date_column, '%r') AS formatted_time FROM table_name [!--sql2--][/div][!--sql3--]





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users