getting time from timestamp

[SkaFreaks]

Hey
I was just wondering if there is a way to get a time from a timestamp. I have a database of songs and all the lengths are timestamps. I want to change those to to times.

Thanks for any help!

[Erikina]

date() (http://www.php.net/date).

Hope that helps.

[Horus_Kol]

What version of MySQL are you using SkaFreaks - because MySQL have changed the Timestamp format since 4.1 - meaning it is not possible to just inject it into a date() function (annoyingly).

[Erikina]

Very interesting. I am still running 4.0.22 - what are the changes in timestamp?

[darksidepuffin]

either way..you should be able to use strtotime or mktime

[Horus_Kol]

http://dev.mysql.com/doc/mysql/en/timestamp-pre-4-1.html
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

TIMESTAMP columns are displayed in the same format as DATETIME columns.


I didn't know that you could still use mktime() on them though...

[darksidepuffin]

http://dev.mysql.com/doc/mysql/en/timestamp-pre-4-1.html
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html




I didn't know that you could still use mktime() on them though...

you'd need to use explode twice more than likely...but yes..you could.

[darksidepuffin]

$dtime = 'YYYY-MM-DD HH:MM:SS';

$dtime_arr = explode(' ',$time);

$date_arr = explode('-',$dtime_arr[0]);
$time_arr = explode(':',$dtime_arr[1]);

$stamp = mktime($time_arr[0],$time_arr[1],$time_arr[2],$date_arr[0],$date_arr[1],$date_arr[2]);


should work.

[Horus_Kol]

Actually, I started using this script I wrote to get a unix timestamp for date() functions:


function date_mysql_to_unix($date_string, $mysql_version)
{
if ($mysql_version < "4.1")
{
$d['Y'] = substr($date_string, 0, 4);
$d['m'] = substr($date_string, 4, 2);
$d['d'] = substr($date_string, 6, 2);
$d['H'] = substr($date_string, 8, 2);
$d['i'] = substr($date_string, 10, 2);
$d['s'] = substr($date_string, 12, 2);

$date_unix = mktime($d['H'], $d['i'], $d['s'], $d['m'], $d['d'], $d['Y']);
}
else
{
$d['Y'] = substr($date_string, 0, 4);
$d['m'] = substr($date_string, 5, 2);
$d['d'] = substr($date_string, 8, 2);
$d['H'] = substr($date_string, 11, 2);
$d['i'] = substr($date_string, 14, 2);
$d['s'] = substr($date_string, 17, 2);

$date_unix = mktime($d['H'], $d['i'], $d['s'], $d['m'], $d['d'], $d['Y']);
}

return $date_unix;
}

It is version independent of MySQL - my database classes get the MySQL version when they interact with the database anyway - and this means that I don't have to worry about what my server and my local setup is doing.

[SkaFreaks]

I'm not sure exactly what version it is. It was installed with SAM2. The help file says its version 3.52 but I'm not sure if that is right. I used the date() but it doesnt give the correct time.

echo date('i:s', 226534);

thats what I used, and that was the timestamp given for one song. with that it gives me '55:34' but the song length is 3:46.

thanks for helping

[scoutt]

stuart you can do a mysql function to get the timestamp in a readable format without all of the code you display there.

do it this way as I suspect it should work for both of you


select (DATE_FORMAT(timecolumn, "%Y-%m-%d")) AS CurrentTime, * from table

OR

select (DATE_FORMAT(timecolumn, "%i-%s")) AS CurrentTime, * from table

of course you can change the names. but that should get what you want

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

[darksidepuffin]

I'm not sure exactly what version it is. It was installed with SAM2. The help file says its version 3.52 but I'm not sure if that is right. I used the date() but it doesnt give the correct time.

echo date('i:s', 226534);

thats what I used, and that was the timestamp given for one song. with that it gives me '55:34' but the song length is 3:46.



Using a timestamp for a preset length doesn't really make much sense -- it's meant to be used for date and time for a valid date...not a specific inputted time. I'm gonna guess that your timestamp is the problem.

[SkaFreaks]

Yes, I believe that was the problem. I used some code that came with SAM to get it working right. I'm not sure exactly how it does it, but it works for me.
Thanks for All the help