mysql insert current date and time

[perhapscwk]

I using a insert query to insert some data into mysql.

I use now() as the current date and time but it always have 3.X hours different from actual date.

what i should use instead?

thanks.

[Atli]

Hi.

Is your server perhaps located +3 time-zones away from your local time?

The NOW() function inserts the current time according to the time on the server machine and the timezone settings on the MySQL server itself.

There are several ways to specify the timezone.
See 9.7. MySQL Server Time Zone Support

If you have root-like (super) privileges on the server, you can issue this command to set the timezone:

SET GLOBAL time_zone = timezone;

If you aren't up for all that, you could just use the DATE_SUB, DATE_ADD or CONVERT_TZ functions to correct the difference in your queries.

Like, if your local timezone is GMT and the server is 3 hours ahead:

/* You could either do: */
SELECT CONVERT_TZ(NOW(), '+03:00', '+00:00');
 
/* Or: */
SELECT DATE_SUB(NOW(), INTERVAL 3 HOUR);

Both would return the date 3 hours prior to the current date on the server.

And you can of course you both of those in an INSERT statement as well.
(Although, I never feel right altering data before INSERTing it. I like to do it on the way out.)

[perhapscwk]

thanks...I will use DATE_ADD instead..thanks alot

[Atli]

Glad I could help :)