Solved

MYSQL Datestamp - PHP

Posted on 2004-10-09
11
981 Views
Last Modified: 2013-12-12
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
Comment
Question by:apcs-uk
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 12

Expert Comment

by:minichicken
ID: 12266687
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12266976
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
 

Author Comment

by:apcs-uk
ID: 12267000
Roonaan, that was the default value inserted by MYSQL as NOW
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12267038
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Expert Comment

by:minichicken
ID: 12267105
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
 
LVL 48

Expert Comment

by:hernst42
ID: 12267299
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
 
LVL 49

Accepted Solution

by:
Roonaan earned 125 total points
ID: 12267432
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
 

Author Comment

by:apcs-uk
ID: 12274675
Roonaan, i will try your code example when i get home tonight.

Many thanks everyone!
0
 
LVL 10

Expert Comment

by:frugle
ID: 12275194
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Please show me easiest post form. 3 31
WordPress Themes 10 34
Problem sending file attachments 8 23
using php variable inside javascript 5 13
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now