Inserting unix timestamp from DB server ?

kid_c

Hello all,

I would like to know if there is any method when using MySQL to INSERT a row containing the UNIX timestamp from the DB server machine in a single query ?

Currently, I have 9 webservers running, so when I do an INSERT like this:

$time = time();

INSERT INTO table SET time='$time'.....";

Sometimes the order they get inserted in is not actually the correct order they should be (ie. the webservers' dates get out of synch sometimes...even if its by 10 seconds or so).

This causes problems for me, as the above is in a PHP/MySQL chat program I coded. Thus a user on one webserver might say something after another, but it will appear before in the display.

I suppose everytime I insert, I can go fetch the time of another machine, but this adds much overhead. Is there anyway in MySQL to use the time of the DB machine ??

Thx

AstroTeg

Have you looked at the MySQL NOW() function? MySQL will take the server's time and automatically put whatever it is in the field...

kid_c

I have tried it, but it only inserts 0.

In MySQL docs, I only saw it used in SELECT examples, thus I just figured it won't work for INSERTS or UPDATES.

AstroTeg

What's your field type? I've used it all the time for inserts, updates, and selects (I found it was a lot easier to remember "now()" then to try remembering PHP's date() with the proper format string).

All my fields have been datetime fields. Hmm... Trick is you're talking Unix timestamp. I just checked, NOW() returns the date and time in the following format:

'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS

Both of those are probably incompatible with a Unix timestamp.

How about this: UNIX_TIMESTAMP()

That returns the current time as a Unix timestamp. If that doesn't work, here's the page I'm browsing for all sorts of date/time MySQL functions:

http://www.mysql.com/doc/en/Date_and_time_functions.html