Date returns 1970

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Yanayaya
Forum Commoner
Posts: 42
Joined: Tue Dec 02, 2008 7:49 am

Date returns 1970

Post by Yanayaya »

Hello,

I am working with a phpbb environment however this issue is not related to that. What I have is a calander which has events stored in it. The date of those events are stored in a table winth mySql called event_day as a varchar.

I wrote some php to pull the data out and also to format the date from that column however if I add ANY formatting of any kind it changes the date to Jan 1970.

Code: Select all

                     $urlPath = "./forum";
	     include './forum/config.php';
	     $table_calendar = $table_prefix. "calendar_events";
	     $link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect"); mysql_select_db("$dbname") or die("Could not select database");
	     $sql="SELECT event_id, event_subject, event_start_time, event_end_time, sort_timestamp, event_day FROM $table_calendar c";
	     $result_d = mysql_query($sql) or die("Woops!");									
	        while ($value_d = mysql_fetch_array($result_d, MYSQL_ASSOC))
		{
		echo "<li>". $value_d["event_subject"] ."<p>".date('d.M.y', $value_d["event_day"]) ."</p></li>";
                    	}
	mysql_free_result($result_d);
	mysql_close($link) 
If you dont add the date('d.m.y') the format comes out just fine. I am not 100% sure what it is that I am doing wrong. The calander I am pulling the data from was a 3rd party mod which leads me to think perhaps the data is stored incorrectly i.e. varchar instead of datetime?!

Either way I have to work with it so is it possible to sort?

many many thanks in advance for any help :)
Idri
Forum Newbie
Posts: 19
Joined: Sun May 29, 2011 9:21 am

Re: Date returns 1970

Post by Idri »

The date function only accepts format and a timestamp. The reason as to why you're getting 1970 as a date is because this resembles a timestamp of 0 (the unix time started in 1970). In order to make it work, you'll first have to convert your varchar date to a timestamp using strtotime.

Though looking at the code I'd suggest you first try inputting the sort_timestamp into the date function, seeing as from the sound of it, it's the actual timestamp you want. If it is, the sorting can be solved by adding ORDER BY(sort_timestamp) ASC (or DESC, depending on what you want).
Yanayaya
Forum Commoner
Posts: 42
Joined: Tue Dec 02, 2008 7:49 am

Re: Date returns 1970

Post by Yanayaya »

Perfect, thank you so much. That works like a charm and you are correct it was the timestamp that I need, I must have been having a blonde moment. :D

Thanks again for the help and info.
Post Reply