"datetime" instead of "unsigned int(10)" ?

[mihai11]

Hi,

I see that VB is using fields of type "unsigned int(10)" instead of "datetime" to store times. Why is that ? What is the problem with "datetime" ?

Regards,
Razvan M.

[Zachery]

Because vBulletin uses a UNIX timestamp, which is not compatable with datetime in mysql

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

[mihai11]

Originally Posted by Zachery

Because vBulletin uses a UNIX timestamp, which is not compatable with datetime in mysql

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

I see ...

If I want to insert test data on my database, what MySql function should I use ? Until now I was using the functions "now()" to insert the current time, but that is not working properly with "unsigned int(10)".

[Zachery]

Honestly I got lucky on that one and googled for an anwser, if you need time though TIMENOW works well from the vBulletin engine, and to anwser your other thread, i believe its generated in init.php.

[mihai11]

Originally Posted by Zachery

Honestly I got lucky on that one and googled for an anwser, if you need time though TIMENOW works well from the vBulletin engine, and to anwser your other thread, i believe its generated in init.php.

You are right ! It is generated like this:

PHP Code:

// set the current unix timestamp
define('TIMENOW', time()); 


[Marco van Herwaarden]

Working with a Unix timestamp is much easier in software. It is easier to sort or do calculations.

Also using TIMENOW instead of now() makes sure that you are using 1 timestamp for the whole execution of the script.

[Eikinskjaldi]

Originally Posted by Marco van Herwaarden

Working with a Unix timestamp is much easier in software. It is easier to sort or do calculations.

Just have to agree to disagree on that one.

Within mysql, to turn a unixtime into a date time, use from_unixtime(val). To turn a datetime into a unixtime, use unix_timestamp(val). You can, for instance, do unix_timestamp(now())

And if you wanted to determine some date range from the timestamp, you can convert to datetime and then use the various mysql functions (which is why I think working with datetime is the preferred option) so for instance if you wanted all the posts from the last 14 days you could use

where to_days(now()) - to_days(from_unixtime(post.dateline)) <= 14.