CODEWITHSUNDEEP
phpmysql
santa
santa

Reputation: 12512

Offset time with MySQL

I have a query that pulls results from two tables. The datetime is stored in one table and time offset in minutes (-300) is in the second table. Is it possible to retrieve already offset local time or do I need to do calculation with PHP?

SELECT
   t1.eventName, 
   t1.eventTime, 
   t2.offsetTime
WHERE id = 2

Event time is stored in the following format: 2015-11-05 02:04:01 and offset time: -360

If I go MySQL route, do I need to convert them to milliseconds first?

Upvotes: 1

Views: 617

Answers (2)

spencer7593
spencer7593

Reputation: 108450

Yes, it's possible to do that calculation in a MySQL expresssion.

If t2.offsetTime is integer datatype representing a number of minutes, and t1.eventTime is datatype of DATETIME, TIMESTAMP or DATE... you can use an expression to add the offset (in minutes) to the datetime.

For example:

  t1.eventTime + INTERVAL t2.offsetTime MINUTE

Just use that expression in the SELECT list, for example:

SELECT t1.eventName
     , t1.eventTime + INTERVAL t2.offsetTime MINUTE AS offsetEventTime
     , t1.eventTime
     , t2.offsetTime
  FROM t1
  JOIN t2
    ON ...

If you prefer to use a function in the expression, MySQL provides the DATE_ADD function which performs the same...

 DATE_ADD(t1.eventTime, INTERVAL t2.offsetTime MINUTE)

(In place of MINUTE, you can use DAY, HOUR, MONTH, SECOND, et al.)

Upvotes: 1

Marty
Marty

Reputation: 39456

There are a load of different functions you can use to achieve this like SUBTIME & ADDTIME.

In your case it might be something like*:

SELECT ADDTIME(t1.eventTime, t2.offsetTime) as newTime

*You may need to use another function to convert -300 into a format like -00:05.

Upvotes: 2

Related Questions