MYSQL Timestamp and PHP

[Shroder01]

I have a field in my mysql database that has a timestamp. It shows as follows:

Timestamp: 20030110102626

I'm using the now() function to enter in the timestamp.
I haven't found a way to get the number back though. Date doesn't work. And I've looked into strftime and strstotime.

Is there a way to extract the timestamp and put it in a certain order without needing a big chunk of coding?

[hand]

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580
mysql> SELECT UNIX_TIMESTAMP('19971004222300');
        -> 875996580

http://www.mysql.com/doc/en/Date_and_time_functions.html

[keith73]

what is the data type of the field?

If it is int, then the date must be converted to integer before being stored. NOW() can return a string or an int depending on how it's being called.

When I need a timestamp in a table, I'll use datetime for the field type. The date gets entered using NOW() and when retrieved I either display it as YYYY-MM-DD HH:MM:SS or I convert it to int using strtotime then call strftime to reformat it.

echo strftime("%m-%d-%Y %H:%M:%S:",strtotime($Result['datefield']));

if you are being returned something like this: 20030110102626 you could use a series of substr() calls to extract the data. This question was posted earlier this week. Here's a function I wrote to handle that.

function reformatTS($tstamp,$format="u"){
	$unix_stamp = strtotime(substr($tstamp,0,4) . "-" . 
	                        substr($tstamp,4,2) . "-" .
							substr($tstamp,6,2) . " " .
	                        substr($tstamp,8,2) . ":" .
							substr($tstamp,10,2) . ":" .
							substr($tstamp,12,2));
	if($format == "u"){
		return($unix_stamp);
	} else if($format == "s") {
		return(strftime("%m-%d-%Y %H:%M:%S",$unix_stamp));
	}
}

• keith

[xblue]

Hi,

I'd always try doing it right within the mysql query, e.g.
SELECT DATE_FORMAT(column_name, '%m/%d/%Y')

The different formatting options are listed here
http://www.mysql.com/doc/en/Date_and_time_functions.html

Hope this helps,
xblue

[Link]

I tryed the DATE_FORMAT one, but the problem is that it only lets you produce pre defined stuff.

For example I want to have somthing that says

Posted by (username) on Day-month-year at the-time

I know how to do the username bit, but formatting the date the way I want has me stumped. Any links/ideas?

[xblue]

you can put any strings you want between the variables, like

SELECT DATE_FORMAT(column_name, 'on %m-%d in %Y') AS fdate FROM table

[Link]

Ah, the plot thickens

cheers xblue

[edit]YARRRRRR IT WORKS...

I have been trying to do this for about 6 (no exageration) hours!!!
xblue, if I ever meet you, I will be buying you a pint. Cheers[/edit]

[xblue]

I don't even like beer, but thanks anyway 🙂

[ape-man]

I use this :

	function sqldate($sqldate, $format) {

	$theyear = substr($sqldate, 0, 2);
	$themonth = substr($sqldate,2,2);
	$theday = substr($sqldate,4,2);
	$thehour = substr($sqldate, 6, 2);
	$theminute = substr($sqldate,8,2);
	$thesecond = substr($sqldate,10,2);

	$finaldate = mktime($thehour,$theminute,$thesecond,$themonth,$theday,$theyear);

	return date("$format", $finaldate);

}

I can't remember where i found the code but i added the $format so i could use different formats all over my site.