MySQL Timestamps and PHP

How to work with them

[Nick_W]

Hi all,

So, the MySQL TIMESTAMP(14) column is different to the PHP date/time functions?

How does one work with these values in PHP, for example: If you have a 14 digit MySQL TIMESTAMP field and want to find out how many hours have passed since the time now?

I guess there is a way of working with these values easily right?

Many thanks

Nick

[jpjones]

Hi Nick,
The MySQL timestamp type is indeed different to PHP.

PHP uses unix time to work out dates.
MySQL uses an ISO standard.

What I've just done when working with times is:

SELECT the field with

UNIX_TIMESTAMP(TimeStampField)

then use the standard php functions to manipulate time as you want it.

E.g. to output the date & time read from the database you would use:

print date("d/M/y H:i",$datefromdatabase);

Hope that points you on the right path!
JP

[Nick_W]

Sure does, that's perfect, thanks!

It's funny, at the back of my mind I remembered them being different and also there being lots of info on how to deal with it.

Could I find it though?

Anyway, thanks again..

Nick

[Jocelyn]

Hi!

I suggest having a look here :
[mysql.com...]

The functions DATE_FORMAT, TIME_FORMAT, NOW, UNIX_TIMESTAMP, FROM_UNIXTIME are especially useful.

> How does one work with these values in PHP, for example: If you have a 14 digit MySQL TIMESTAMP field and want to find out how many hours have passed since the time now?

The timestamps are given in seconds since q reference date. Substract them, divide by 3600 to get the number of hours then truncate that value to the lower integer.
SELECT FLOOR((NOW() - my_timestamp)/3600) FROM my_table WHERE ...

The FLOOR function is here : [mysql.com...]

Jocelyn

[Allen]

Also check the php.net manual under date and time functions.
There's lots of notes there.

Allen

[Nick_W]

Jocelyn, that's superb, thanks!

Now everything is much easier and it's a snap to have the timestamp fields return usefull values with UNIX_TIMESTAMP()

Many thanks

Nick

[dmorison]

As an aside, when writing simple "database driven" websites, in many cases you are better off not bothering with your database's native DATE/TIME support and instead simply store the time() value straight into the database as an INT.

The decider is normally how you are going to query the database - if your query includes calculations based on the time value then use DATE/TIME.

However if you're SELECTing based on some other criteria and are just going to process the date/time in PHP then simply store the INT.

[DrDoc]

That's a very good point you're making, dmorison!