SQL time issue - need it 6 hours forward!

Question

Possible Duplicate:
Add 2 hours to current time in MySQL?

My server is currently based on us eastern time, and as I am in the UK, i need to move this forward 6 hours.

When using now() in mysql, i am getting the wrong time, is there anything i can add to now() to bring it forward 6 hours? Thanks

Answer 1

You want to use the DateAdd function:

Example:

SELECT DATEADD(hour, 6, GetDate())

Answer 2

Ref this

SELECT ADDTIME(now(), '06:00:00')

SEE IT HERE WITH DIFFERNCE

SELECT ADDTIME(now(), '06:00:00') as EAST_TIME , now() as UK_TIME

Answer 3

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'.

Answer 4

Yes, you can use the date_add function:

date_add(now(), interval 6 hour)

You can also use adddate which is an alias for date_add.

Consider also to store the time as UTC in the database, and convert to local time when you display it.

If the conversion is from one time zone to another rather than a set number of hours, you can use the convert_tz function. Example:

convert_tz(now(), '-6:00', '0:00')

or:

convert_tz(now(), 'US/Eastern', 'MET')