Avatar of freestate
freestate
 asked on

PHP: Convert from MySQL timestamp (yyyymmddhhiiss) to date

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
PHPDatabasesMySQL Server

Avatar of undefined
Last Comment
freestate

8/22/2022 - Mon
Pratima

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

$date_format=date("m:d:y",$time_stamp); // formats timestamp in mm:dd:yy  
 
print $date_format; // results here ... 02 : 11 : 07
Andrej Pirman

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

 
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Pratima

freestate

ASKER
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
Pratima

try this
$date = "20110307121543";

echo sprintf("%02s:%02s:%02s", substr($date,4,2),substr($date,6,2),substr($date,2,2));
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Pratima

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
freestate

ASKER
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
Chris Harte

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"));
freestate

ASKER
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);
?>
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
freestate

ASKER
Sorry - just noticed your print_r - however, still get the undefined function error...
freestate

ASKER
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

Pratima

glad to know it helps you
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
adeelshahid

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
freestate

ASKER
Had to adapt the suggested expert solution to get where I needed to be