12 replies to this topic

[Av-]

So yeah, I usually just use the date() function, but the lack of abilty to calculate timezones and stuff has been buggering me more and more lately. So i was looking at the PHPBB and WBB mysql table structure and i saw they use a series of numbers for their post date. Id reckon those are timestamps.

So how would i go about creating a timestamp and converting it into readable time and date.
Please push me in the right direction by reffering me to the function on PHP.net

Thanks

[Matthew.]

Easy as pie mate Basically uou use date(). soemthing you will be fimiliar with it seems.

echo date("M jS Y", $timestamp);

$timestamp being timestamp

Im sure you know the url http://uk.php.net/date

Edited by .Matt, 18 May 2006 - 03:03 PM.

[Av-]

Yeah, thats not what i meant. If you look at phpbb's mysql table you will see a series of numbers for the date, like 11020233 (I think its called a UNIX Timestamp??), then they convert it back to normal readable time and add more or less hours for different timezones.

[Matthew.]

yep, look.

$timestamp = time();
// at 21:23 this produces: 1147984321

the numbers (unix timestamp) are created by time();

To add days/weeks etc its like this:

$plusweek = time() +(7 * 24 * 60 * 60);
echo date("M jS Y", $timestamp);

Edited by .Matt, 18 May 2006 - 03:33 PM.

[coolaid]

matt, could you explain what each line does. what does the multiplication give you? and how can you use the timestamp with the date???

[Matthew.]

Ok,

the date function, as shown on the PHP manual has too "variables" (quotes, cuz i dunno the word)

Quote

date ( string format [, int timestamp] )

Usage:

date("date format", timestamp);

You can of course use date without the timestamp part as standard.

For the multiplicaton part its even simpler, +7 x 24 x 60 x60 is 7 days x 24 hours x 60 mins x 60 seconds.

Its just a week broken down basically, there are 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day and 7 days in a week. This produces 604800.
So now you see if you add 604800 onto a current timestamp then you get a timestamp that you will get one week into the future (exact to the second).

Example:

<?php

$plusweek = time() + (7 * 24 * 60 * 60);
echo "The current timestamp at ".date('l dS \of F Y h:i:s A')." is: ".time();

echo "<br />The timestamp one week into the future (at ".date('l dS \of F Y h:i:s A', $plusweek)." will be: ".$plusweek."<br /><br /><strong>";

echo time() + 604800 ." </strong>Here we see adding time() and 604800 is the same as the above";

?>

At the moment this produces:

Quote

The current timestamp at Thursday 18th of May 2006 03:28:53 PM is: 1147987733
The timestamp one week into the future (at Thursday 25th of May 2006 03:28:53 PM will be: 1148592533

1148592533 Here we see adding time() and 604800 is the same as the above

Edited by .Matt, 18 May 2006 - 04:30 PM.

[Av-]

wow, thats kind of confusing lol, i willlook into it.
one more question, what the mysql column type DATE used for?

[rc69]

MySQL can automatically insert a timestamp for you, i don't know which type does it, but it can. Personally, i find using php and time() is easier to keep track of.

There's a ton of tutorials in the database on this if you want to learn more.

[Matthew.]

Never rely on mysql to insert a time. Always use php Avalance

[Av-]

yes, thanks for your post matt, very well explained, I now understand how to add hours for different timezones too.

so problem is solved, thanks all

[Marxx]

Hi, I don't actually know is my problem related to this but I'm creating script where I need to show dates and times.
Unfortually all dates are format YYYY-MM-DD and those should be format like this DD-MM-YYYY..
How can I fix this?

Thanks for all!

[Demonslay]

Ah, date and time, fun stuff to mess with...

If you are storing it in MySQL, use the DATETIME (or TIMESTAMP, or DATE) type, and to insert the current date you can either use the CURRENT_TIME variable in MySQL, or the NOW() function. To insert a Unix timestamp (from the PHP time() function for example), use FROM_UNIXTIME() in MySQL.

INSERT INTO `table`(`id`, `time`) VALUES(NULL, FROM_UNIXTIME($timestamp))

Then, to retrieve it, use UNIX_TIMESTAMP() and an alias.

SELECT UNIX_TIMESTAMP(`time`) AS timestamp FROM `table`

Now some of you may argue that it may be easier to insert it as an integer directly from the timestamp. While that might be true in some cases, you won't be able to use MySQL's date comparison functions and such.

To Marxx.
One method would be to split the variable up and re-arrange it. Provided the format is exactly the same every time, you could do it like this.

function formatDate($yy_mm_dd){
  $date = explode('-', $yy_mm_dd);
  return "{$date[2]}-{$date[1]}-{$date[0]}";
}

Or a different variation of the same result.

function formatDate($yy_mm_dd){
  return implode('-', array_reverse(explode('-', $yy_mm_dd)));
}

For timezones and such for anyone else wondering, you have to find the server's offset from GMT, and then the client's offset from GMT, and pretty much add them all together.
To get the server's offset you can use date('Z'), and the client's you will probably have to get it from JavaScript or a user preference setting. Something which I will leave to your discretion.

echo time() + date('Z') + $client_offset;

Then of course, you have DST. This is used in PHPBB as a kind of switch, and they add it onto the end of that end time, and if it is in effect for the user (from the user's preferences), then it add 3600 seconds (1 hour). Or is it subtracted...

Either way, if you want to learn more on that, you can view the source code for the user::format_date() function in the PHPBB3 distribution. They also have some calculations and fun stuff with midnight and the 'Today', 'Yesterday', and 'Tomorrow' support like seen on most forums.

Edited by Demonslay, 15 August 2007 - 04:57 PM.

[Marxx]

Thank you demonslay for answering.. Helped me alot.
I have one thing tho whats bugs me.

If time is like this: 2007-08-24 11:44:23

How do I convert it like this: 24-08-2007 11:44
I have trying to solve this by using your example above but haven't got it yet..
Or even better if there is way to put it in database in that form at the begining. If you understand what i'm saying?
Now I use NOW() when I'm putting date and time into database..

Thanks for all help!

//EDIT

Okey guys.. I solved this one!
I'll paste it in here and if someone finds something in it, please share it whit me.
Best part about this is that it works for me!

 function format_date($yy_mm_dd){

  $getdate = explode(' ', $yy_mm_dd);

  $date = explode('-', $getdate[0]);
  $date = "{$date[2]}.{$date[1]}.{$date[0]}";

  $gettime = explode(':', $getdate[1]);
  $time = "{$gettime[0]}:{$gettime[1]}";

  $datetime = $date . " " . $time;

$today = date("d.m.Y");
$yesterday = date("d.m.Y", strtotime("-1 day"));

if ($date == $today) {
return "Today".$time."";
}
elseif ($date == $yesterday) {
return "Yesterday".$time."";
}
else {
return $datetime;
}

}

Oh yes, there is also this today, yesterday thing..
If result is today: Today 12:32
If yesterday: Yesterday 12:32
after that: 22.08.2007 12:32

Edited by Marxx, 24 August 2007 - 10:35 AM.