• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1086
  • Last Modified:

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
0
apcs-uk
Asked:
apcs-uk
  • 3
  • 2
  • 2
  • +2
1 Solution
 
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
 
apcs-ukAuthor Commented:
Roonaan, that was the default value inserted by MYSQL as NOW
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
RoonaanCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now