MYSQL Datestamp - PHP

Hi there,

I have a date stored in a 'datestamp' field like this

20040929101833

How can i pull that into my php page as a normal date and time, and as a date on its own (without the time)

For example, from a number like this 20040929101833

I need to get (these dates are just examples of the formats i need)

"13:08, 24th Aug 2004"
and "24 Aug 2004"

Many thanks
apcs-ukAsked:
Who is Participating?
 
RoonaanConnect With a Mentor Commented:
What about
$time = '20040929101833';

$date = strtotime(ereg_replace('^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$',"\\1-\\2-\\3 \\4:\\5:\\6", $time);

echo date('Ymd H:i:s', $date);

Regards -r-
0
 
RoonaanCommented:
Possibly you could strtotime() function, but I am not sure if that one reads this type of date-representation.

I always thought Mysql used "2004-09-29" (date), "2004-09-29 10:18:33" (datetime) and the unix timestamp which count's the seconds from the unix day zero.

Is this a type you created yourself, or did I miss one?

Regards

-r-
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
apcs-ukAuthor Commented:
Roonaan, that was the default value inserted by MYSQL as NOW
0
 
RoonaanCommented:
Depends, in which columntype you deposit. That's why I ment to ask; which type of column you used: date, datetime, timestamp or something else.

Regards

-r-
0
 
minichickenCommented:
I tried to used timestamp with the strtotime(); and date(); it doesnt seem to work.

I would recommend the use of DATE and DATETIME column type... should be easier to work with...
0
 
hernst42Commented:
You can use
Select UNIX_TIMESTAMP(column) as column from table
to get your dates so you can use it directly with the php-date function
0
 
apcs-ukAuthor Commented:
Roonaan, i will try your code example when i get home tonight.

Many thanks everyone!
0
 
frugleCommented:
I use SQL to do the work, rather than obfuscated regex's...

SELECT *,DATE_FORMAT(datestamp_field,'%D, %M %Y') AS realdate FROM mytable;

- Seems quicker than having php do after-processing when returning a lot (10,000+) of rows.

I'm sure someone will have proper benchmarks to prove me wrong though...

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.