Splitting a MySQL timestamp

radley

Hi, I'm making a news manager, and i'm using a timestamp(14) field with the name stamp. What i'm trying to do is split the timestamp into year, month, day, hour, minute, and second.

Anon

assuming the timestamp is in this format (which i believe is default for mysql): 2002-03-16 05:10:34
you could do:

$my_timestamp = $row["timestamp"];

list ( $date_string, $time_string ) = explode (" ", $my_timestamp);

list( $my_year, $my_month, $my_day ) = explode ("-", $date_string);

list( $my_hour, $my_minute, $my_second ) = explode (":", $time_string);

the variables you want would then be $my_year, $my_month, $my_day, $my_hour, $my_minute and $my_second.

Hope this helps.

-Jim Keller
http://www.centerfuse.net

radley

That wont work because i'm using a timestamp field. The format is yyyymmddhhmmss

van__

Well, that aughta teach you for using timestamp(14)!!!!!!!!!!!!!

: )

But to solve your problem...

$stmt = "SELECT DAYNAME(dateCol) as weekDay, DAYOFMONTH(dateCol) as date, MONTHNAME(dateCol) as monthString, MONTH(dateCol) as monthNum, YEAR(dateCol) as year FROM myTable ORDER BY UNIX_TIMESTAMP(dateCol)";

all ordered by date
enjoy

van__

Oh, I forgot to include your time settings too...

just add in:
HOUR(dateCol) as hours, MINUTE(dateCol) as minutes, SECOND(dateCol) as seconds

if you hadn't guessed already.

radley

okay, i understand how it works, but how would you access it? if you use a mysql_fetch_assoc() would they be included? Like if $myfield was mysql_fetch_assoc() MINUTE(dateCol) as minutes would be $myfield["minutes"]?

van__

Radley wrote:

okay, i understand how it works, but how would you access it? if you use a mysql_fetch_assoc() would they be included? Like if $myfield was mysql_fetch_assoc() MINUTE(dateCol) as minutes would be $myfield["minutes"]?

Exactly.
When you use a SQL function within your statement, you use the "as" modifier to instruct MySQL to refer to the returned value as the reference supplied.

radley

okay thanks alot, thats helps a bunch!