0

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

Community
  • 1
  • 1
sark9012
  • 5,485
  • 18
  • 61
  • 99
  • 3
    This kind of depends on your app, but in many cases it's a good idea to store all times in UTC and do the timezone conversions in the UI. That way you don't have to worry about your server's timezones and anything like that. – Matti Virkkunen Jun 03 '10 at 13:55
  • ++ Matti. If you use local times, they'll all be off by an hour as your enter/leave day light savings. Trust me, it's bad. Use UTC. – DougN Jun 03 '10 at 13:57
  • Whoa! Watch out! Just adding 6 hours will not work in the general case because in the USA we have daylight saving time. The hour offset is not always 6 hours. Sometimes it's five. So you need a function that will convert time based on timezones. – Dave Markle Jun 03 '10 at 13:58
  • Using UTC sounds like a contrived solution, since it still requires date conversion all over the place. Doesn't MySQL have the `DateTimeOffset` data type, which stores a date in absolute form while retaining the time zone information? – bzlm Jun 03 '10 at 14:00
  • @bzlm: Human timekeeping method are always contrived. – Matti Virkkunen Jun 03 '10 at 14:02
  • This kind of nonsense is why I personally never use database time datatypes. UTC-relative timestamp in an integer column; convert to user's chosen timezone on formatting and parsing in the UI layer; job done with no dependency on the server's timezone. – bobince Jun 03 '10 at 14:22
  • 2
    @bobince: A proper DATETIME type will not introduce any "nonsense" if you use it properly. It even lets you use the handy built-in SQL date calculation functions in most databases, and in languages that support such a type, they tend to get automatically converted to a proper, manipulatable datetime type in your actual code too. – Matti Virkkunen Jun 03 '10 at 14:25
  • Unfortunately, in Python, they get converted to the “naïve” datetime type, which brings with it many further problems. It's a losing proposition all round. Timezone locales are complicated; the data storage layer does not have enough intelligence to cope with them and shouldn't try. – bobince Jun 03 '10 at 14:47
  • 1
    @bobince: It's not the database's fault if Python fails at datetimes. – Matti Virkkunen Jun 03 '10 at 14:48

4 Answers4

1

You want to use the DateAdd function:

Example:

SELECT DATEADD(hour, 6, GetDate())
Darknight
  • 2,460
  • 2
  • 22
  • 26
1

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
Salil
  • 46,566
  • 21
  • 122
  • 156
1

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')
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
0

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'.

Gary
  • 2,866
  • 1
  • 17
  • 20