Date and Time functions in PHP & MySQL

Question

I have date and time stored in my database as MySQL DATETIME datatype. While inserting into the database, I am using the following PHP variable

$serverTime = strftime("%Y-%m-%d %H:%M:%S",$_SERVER['REQUEST_TIME']);

A sample DATETIME stored in database is: 2011-11-26 01:00:27

Now my website needs the date to be displayed in the following format: November 26, 2011 (time is not required)

How can I do it? I am trying the below in PHP but wrong output I am getting.

echo strftime("%B %d, %Y",$serverTime)

So many duplicates in the "related" column. Also, what exactly is wrong about the output you are getting? The format looks okay so far — Pekka
– Pekka, Commented Nov 25, 2011 at 20:59
@Pekka: I am getting January 01, 1970 output...no matter what the $serverTime variable is. — sumit
– sumit, Commented Nov 25, 2011 at 21:01

Bill Karwin · Accepted Answer · 2011-11-25 21:08:55Z

5

The PHP strftime() function wants input to be a 32-bit integer timestamp, which is the number of seconds since 1970-01-01 00:00:00.

The default output format for MySQL datetime is YYYY-MM-DD HH:MM:SS. When you use this string in PHP, the leading digits are converted into the integer 2011, which is interpreted to be within the first hour of 1970-01-01.

So you must fetch the datetime from MySQL in another format. Choices:

UNIX_TIMESTAMP(datetime_column) to fetch the datetime as an integer that you can give to PHP's strftime() function.
DATE_FORMAT('datetime_column', '%M %d, %Y') to format the datetime as a string, and then you don't have to use strftime() to format it.

answered Nov 25, 2011 at 21:08

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pelshoff Over a year ago

Another option would be to parse the date format that the db returns with to strtotime() function :)

Bill Karwin Over a year ago

I don't count that as an option. :-) I try to avoid parsing string representations of numeric data when I can, and this is a case where it's easy to avoid that.

nickb · Accepted Answer · 2011-11-25 21:14:57Z

1

While I would recommend a completely SQL approach, you can use PHP's DateTime class to process your string in PHP (assuming you're running PHP > 5.3):

// Pick a valid TimeZone
$date_obj = DateTime::createFromFormat( 'Y-m-d H:i:s', '2011-11-26 01:00:27', new DateTimeZone( 'America/New_York'));
echo $date_obj->format('F d, Y');

Demo

answered Nov 25, 2011 at 21:14

nickb

59.7k13 gold badges115 silver badges149 bronze badges

Collectives™ on Stack Overflow

Date and Time functions in PHP & MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related