4

I need to calculated difference in seconds between 2 dates. I cannot use TIMEDIFF because of its limitations.

When I use:

SELECT UNIX_TIMESTAMP('2015-03-28 08:21:15') - UNIX_TIMESTAMP('2015-03-27 08:21:15');

it returns expected 86400 seconds (what gives 24 hours) but when I use:

SELECT UNIX_TIMESTAMP('2015-03-29 08:21:15') - UNIX_TIMESTAMP('2015-03-27 08:21:15');

it seems it doesn't give 86400*2 but 169200 instead what gives 47 hours.

The question is - why is that? Is it a bug or feature? Is there any other reasonable way to calculate time difference not worrying about time limitations?

Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
  • It is not clear to me from your link about what limitations you are talking about. Also `select TIMEDIFF('2015-03-29 08:21:15', '2015-03-27 08:21:15')` gives `48:00:00`. And additionally the idea that you manually try to invent a time difference algorithm sounds like a terrible idea bout YMMV. – Micha Wiedenmann Jul 10 '18 at 07:59
  • @MichaWiedenmann Those dates are only sample. I have much longer periods. – Marcin Nabiałek Jul 10 '18 at 08:01
  • Surprisingly, on my machine it outputs 172800, which equals to 86400*2. OS: Windows XP, MySQL 5.5.25a. – user4035 Jul 10 '18 at 08:02
  • UNIX_TIMESTAMP is timezone specific. – skelwa Jul 10 '18 at 08:03

2 Answers2

3

It looks like that in 2015 there was Daylight changing at 29 March, it could be the reason Daylight saving 2015

Kirill Staruhin
  • 137
  • 6
  • This answer, if read in conjunction with [this question](https://stackoverflow.com/q/1646171/2298301) would make sense. If we were to look at individual values, `UNIX_TIMESTAMP` conversion over `2015-03-28 08:21:15` returns `1427527275` and `2015-03-29 08:21:15` returns `1427610075`. Difference of `82800`, translating into `23` hours. – Dhruv Saxena Jul 10 '18 at 08:08
  • @MarcinNabiałek, just to _test_ one of the answers given in the linked question above: http://rextester.com/RBU19181. Of course, it's based on an assumption that no consideration is required for Daylight Savings. – Dhruv Saxena Jul 10 '18 at 08:15
  • Thanks. Yes, it's really Daylight saving problem. But how would it be possible to calculate it without it? @DhruvSaxena any idea how to calculate it without getting into limit of `838:59:59` of `TIMEDIFF`? – Marcin Nabiałek Jul 10 '18 at 18:03
1

There are a couple of ways in which this problem can be tackled:

With UNIX_TIMESTAMP():

Use SET SESSION time_zone = '+0:00' before carrying out date operations. 

SET SESSION time_zone = '+0:00';
SELECT UNIX_TIMESTAMP('2015-03-29 08:21:15') - UNIX_TIMESTAMP('2015-03-27 08:21:15');

This returns 172800, i.e. a proper time difference of 48 hours.
Demo.

With TIMESTAMPDIFF():

If you don't wish to use SET SESSION time_zone, you could try TIMESTAMPDIFF()

SELECT UNIX_TIMESTAMP('2015-03-29 08:21:15') - UNIX_TIMESTAMP('2015-03-27 08:21:15');
SELECT TIMESTAMPDIFF(SECOND, '2015-03-27 08:21:15', '2015-03-29 08:21:15');

The first computation results in 169200 (i.e. 47 hours) and the second shows 172800 (i.e. 48 hours).

Remember though that the smaller timestamp should be used first in TIMESTAMPDIFF(), or you'd get a negative result. Alternatively, you could wrap TIMESTAMPDIFF() under ABS() as so:

SELECT ABS(TIMESTAMPDIFF(SECOND, '2015-03-29 08:21:15', '2015-03-27 08:21:15'));

Rextester link.

Dhruv Saxena
  • 1,336
  • 2
  • 12
  • 29