converting timestamp...

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

onefocus99
Forum Newbie
Posts: 16
Joined: Tue Oct 07, 2003 11:09 pm
Location: Hamilton Ont., Montreal, Chibougamau,... PQ, Victoria BC, now Alberta Canada

convert from timestamp to php to human time

Post by onefocus99 »

This is what I used and it works great :!: :D :wink:

:idea:
$result = mysql_query("SELECT * FROM $table ORDER BY ID", $db);
:...

function timestamp_php($date)
{
$year = substr($date,0,4);
$month = substr($date,4,2);
$day = substr($date,6,2);
$hour = substr($date,8,2);
$minute = substr($date,10,2);
$second = substr($date,12,2); //below must be in this order
$datetime = mktime($hour,$minute,$second,$month,$day,$year);
return $datetime;
}

while( $myrow = mysql_fetch_array($result) )
{
$the_date = $myrow[lastDate];
$newtime1 = timestamp_php($the_date);
$newtime2 = date('l F jS, Y -- g:ia T', $newtime1);
echo $newtime2
//which prints: Sunday November 23rd, 2003 -- 7:01am PST
}
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: convert from timestamp to php to human time

Post by Weirdan »

onefocus99 wrote:This is what I used and it works great :!: :D :wink:

:idea:
$result = mysql_query("SELECT * FROM $table ORDER BY ID", $db);
:...

function timestamp_php($date)
{
$year = substr($date,0,4);
$month = substr($date,4,2);
$day = substr($date,6,2);
$hour = substr($date,8,2);
$minute = substr($date,10,2);
$second = substr($date,12,2); //below must be in this order
$datetime = mktime($hour,$minute,$second,$month,$day,$year);
return $datetime;
}

while( $myrow = mysql_fetch_array($result) )
{
$the_date = $myrow[lastDate];
$newtime1 = timestamp_php($the_date);
$newtime2 = date('l F jS, Y -- g:ia T', $newtime1);
echo $newtime2
//which prints: Sunday November 23rd, 2003 -- 7:01am PST
}
Seems like you are reinventing the wheel. There are a lot of datetime functions in mysql and php. To retreive formatted date from mysql database you can use [mysql_man]date_format[/mysql_man] and [mysql_man]time_format[/mysql_man] functions. If you need unix timestamp in your php script from db you can use [mysql_man]unix_timestamp[/mysql_man] function. To convert the dates in php from one format to another you can use [php_man]strtotime[/php_man] in conjuction with [php_man]date[/php_man] and so on....

[edit]
2admins: why don't you fix [mysql_man] bbtag?
[/edit]
webcan
Forum Commoner
Posts: 66
Joined: Tue Oct 28, 2003 2:25 pm
Location: Toronto, Canada

Post by webcan »

Hi there - I'm having the same problem that the original poster had, but I can't figure out how to implement this solution into my situation.

I am storing the date and time using the sql timestamp format (so its stored as 20031206094123 for example). In my php application, i want to convert it to usable information. I don't know how to incorporate it into the SELECT statement because I need it to SELECT *, not just the date field.

Is there no easy way to convert the timestamp into something PHP can use once its retrieved, or into a unix timestamp so that I can use date() on it?

Thanks,
Peter.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

You can use [php_man]strtotime[/php_man] function to convert the mysql-formatted timestamp to unix timestamp.

PS: It's usually a bad idea to SELECT *...

[edit]
Just an example:

Code: Select all

function mysql2unix($m){
    $q=preg_split("//",substr($m,8),-1,PREG_SPLIT_NO_EMPTY);
    foreach(array_chunk($q,2) as $item)
        @$time.=(strlen($time)?":":"").implode("",$item);
    return strtotime(substr($m,0,8)." $time");
}
[/edit]
webcan
Forum Commoner
Posts: 66
Joined: Tue Oct 28, 2003 2:25 pm
Location: Toronto, Canada

Post by webcan »

Thanks for the reply.

Why do you say it is a bad idea to SELECT *?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

There was a good thread in mysql-internals list:
http://lists.mysql.com/internals/10970
Alexander Keremidarski wrote: [....skipped....]
No because this is against both SQL Standard and Relational Model spirit.
[....skipped....]
It is not that easy to explain why, but you should never ask such questions.
Adam Hooper wrote: The way I see it, "SELECT * FROM" is an exception from normal SQL
queries: it is to aid in understanding table design and contents rather
than actually get data from them. I think of it as a handy shortcut when
using the command-line client. You're not supposed to use it in code.
webcan
Forum Commoner
Posts: 66
Joined: Tue Oct 28, 2003 2:25 pm
Location: Toronto, Canada

Post by webcan »

Thanks for your response.
Post Reply