0

I'm currently working on a project where I'm recording times into my database, and I want to store the difference between the two times as well. I implemented them using the SQL object: TIMESTAMP and recorded the timestamp using 

TIMESTAMP(CURDATE(),CURTIME())

These store fine. Now I want to compute the difference between the two times, but it looks like PHP's TIMESTAMPDIFF() function takes in datetime objects instead of timestamp objects. There are a few ways I could move on from here, but I was wondering if there's a preferred way that SQL developers record and compute time differences. I need both the date and the time, so that I can get the difference accurate to the second.

Tom
  • 350
  • 4
  • 21
  • Is the difference computed between two columns which already exist in your table? If so I might opt for computing the difference when you query, rather than storing a computed column which wastes space. – Tim Biegeleisen Jun 19 '16 at 03:44
  • OK. I'm still unsure the best way to store and compute these things though. – Tom Jun 19 '16 at 04:00
  • Are you recording one timestamp or two of them (neglecting the differences) ? Can you show us a sample table, along with the differences you want to compute? – Tim Biegeleisen Jun 19 '16 at 04:03

1 Answers1

0

This seems to be a 2 part question and is definitely duplicates of other questions on SO. A quick search produced a SO question that over 1000 people voted for DATETIME as the storage type (Should I use field 'datetime' or 'timestamp'?)

The main difference between DATETIME and TIMESTAMP in mysql is that TIMESTAMP will store that date at UTC and datetime will store your specific value. So if you don't need to translate between time zones, or have multiple time zones etc. Stick with DATETIME and the you don't have to convert or cast or anything.

As far as getting the difference between 2 datetimes or 2 timestamps I am certain their are SO questions on here I suggest searching a little more.

Community
  • 1
  • 1
Matt
  • 13,833
  • 2
  • 16
  • 28