Solved

PHP: Convert from MySQL timestamp (yyyymmddhhiiss) to date

Posted on 2011-03-07
15
796 Views
Last Modified: 2012-05-11
In a database, signup times are stored as a timestamp field, which means the date is stored as follows:

20110307120000

I need to be able to display these using PHPs date function - is there an easy way to do this?

strtotime on this value just gives the value for 01/01/1970...

Thanks
0
Comment
Question by:freestate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35055259
echo sprintf("%02s:%02s:%02s", substr($date,4,2),substr($date,6,2),substr($date,2,2));
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35055264
$date_format=date("m:d:y",$time_stamp); // formats timestamp in mm:dd:yy  
 
print $date_format; // results here ... 02 : 11 : 07
0
 
LVL 18

Expert Comment

by:Andrej Pirman
ID: 35055286
Hi,
inside PHP you can convert simply with date() function:
$date_format=date("m:d:y",$time_stamp); // formats timestamp in mm:dd:yy

Open in new window

or inside MySQL, for example:
# $select = array();
# $select[] = "MONTH(timeStamp) AS theMonth";
# $select[] = "DAY(timeStamp) AS theDay";

...then print it out with PHP:
<?php
echo $results["theMonth"]."/".$results["theDay"];
?>

Open in new window

The rest you can fing in PHP docs under date() function or in MySQL docs under TIME or TIMESTAMP

 
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35055288
0
 

Author Comment

by:freestate
ID: 35055311
Hi - sorry, but omitting strtotime just moves the problem to the other end of the spectrum and results in 19-01-2038 as the output date regardless of what time is entered.

ie.

$timestamp = "20110307121543";
echo date("d-m-Y", $timestamp);

(the other route, which gives 01-01-1970 was:

$timestamp = "20110307121543";
echo date("d-m-Y", strtotime($timestamp));

)

What I need, is 07-03-2011
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35055348
try this
$date = "20110307121543";

echo sprintf("%02s:%02s:%02s", substr($date,4,2),substr($date,6,2),substr($date,2,2));
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 250 total points
ID: 35055362
try this

for your output 07-03-2011
$date = "20110307121543";

echo sprintf("%02s-%02s-%02s", substr($date,6,2),substr($date,4,2),substr($date,0,4));
0
 

Author Comment

by:freestate
ID: 35055536
Hi,

That gives me the print out, but I'm actually passing this to another date function to give me the date in long form (March 7th, 2011) - is there a way of getting that output as a string so I can then pass it to date()?

Thanks
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 35055618
For clarification, the timestamp is the number of seconds since the beginning of the epoch. The value you have 20110307120000, is the date and time stored as a string in year month day format ie.

2011/03/07:12:00:00

you use date_parse() to get this.

print_r(date_parse("20110307120000"));
0
 

Author Comment

by:freestate
ID: 35055664
I'm aware that the UNIX timestamp is seconds since the beginning of the epoch - however the value provided is the value that gets stored in my MySQL timestamp field - does that mean my hosts have the server setup wrong?

Tried using 'date_parse' but am getting an undefined function error?

<?
$timestamp = "20110307121543";
echo date_parse($timestamp);
?>
0
 

Author Comment

by:freestate
ID: 35055668
Sorry - just noticed your print_r - however, still get the undefined function error...
0
 

Author Comment

by:freestate
ID: 35055843
OK -  so tweaking pratima_mcs's offering seems to do the trick:

$timestamp = "20110307121543";
$newdate = sprintf("%02s-%02s-%02s", substr($timestamp,0,4),substr($timestamp,4,2),substr($timestamp,6,2));
echo date('F d, Y', strtotime($newdate));

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35055925
glad to know it helps you
0
 
LVL 2

Assisted Solution

by:adeelshahid
adeelshahid earned 250 total points
ID: 35056768
just use this for date and time.

$date_time = (string)20110307120000;
$year = substr($date_time, 0, 4);
$month = substr($date_time, 4, 2);
$date = substr($date_time, 6, 2);
$hour = substr($date_time, 8, 2);
$minute = substr($date_time, 10, 2);
$second = substr($date_time, 12, 2);

$timestamp = mktime($hour, $minute, $second, $month, $date, $year);
echo date('Y-m-d H:i:s', $timestamp);
0
 

Author Closing Comment

by:freestate
ID: 35056844
Had to adapt the suggested expert solution to get where I needed to be
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

635 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