mysql – Display past dates before the year 1800 – Education Career Blog

I have a system for history events. It’s showing the date using this

    date("j F, Y", strtotime($row'dated'));

But when I try: 21 Oct 1877, it’s showing 31 December, 1969.

How can I fix this problem?

,

You can ask MySQL to format the date the way you want so that you don’t have to convert the formatted date to a timestamp then back to a formatted date.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

,

Use that


$date = new DateTime($row'dated');
echo $date->format('Y-m-d H:i:s');

from here http://www.php.net/manual/en/datetime.format.php

,

The problem is, that strtotime converts the dates to the unix_timestamp, which is the number of seconds since 1970-01-01 00:00:00 UTC. On unix_timestamp ‘0’, it was still 31 December 1969 in USA.

Like @Dan Grossman told you, try to get the right format directly from MySQL. For example:

SELECT DATE_FORMAT(dated, '%d %M, %Y')
FROM history

,

Since you tagged this MySQL, I’d suggest to use the DATE or DATETIME types for representing a date/date and time, as the UNIX timestamp has severe range limitations (can only represent dates between years 1901-2038, as opposed to DATE‘s 1001-9999).

Straight from the manual page of strtotime:

Note:

The valid range of a timestamp is
typically from Fri, 13 Dec 1901
20:45:54 UTC to Tue, 19 Jan 2038
03:14:07 UTC. (These are the dates
that correspond to the minimum and
maximum values for a 32-bit signed
integer.) Additionally, not all
platforms support negative timestamps,
therefore your date range may be
limited to no earlier than the Unix
epoch. This means that e.g. dates
prior to Jan 1, 1970 will not work on
Windows, some Linux distributions, and
a few other operating systems. PHP
5.1.0 and newer versions overcome this limitation though.

In other words, UNIX timestamp is a signed 32-bit integer, and thus can only express cca 137 year span (1901-2038). If you want to do this in PHP, you should be looking at something like the Calendar functions.

Probably a better solution would be to use the MySQL’s DATETIME type (instead of whatever string type you seem to be using), and use the DATE_FORMAT() to format the date.

Note also that various countries have switched to today’s most common Gregorian calendar at various times – e.g. Russia was still using the Julian calendar in the 19th century (several days’ worth of difference between the two).

,

Piskvor and eumiro described why this happens, so i won’t repeat that. what you could do to avoid this is to write your own litte function to format the date – just use explode to get all parts of your date and rebuild the needet format from that parts like this:

list($year,$month,$day,$hour,$minute,$second) = explode('-',str_replace(array(' ',':'),'-',$row'dated');
$output = "$day $month, $year";

this isn’t perfect jet, it outputs the date as number – but you get the idea (you could use an array for storing the monthnames and output $monthname$month instead of $month)

but: also, you could let the database format the date for you, so you just have to output the result – i think this wich would be the best idea (i don’t know the syntax out of my head, ask google for that)

Leave a Comment