1

I am trying to find out which data type is better to store time data inside either MySQL or Cassandra database for a big application like Facebook or Instagram? There are a lot of similar questions and answers but I couldn't finally realize which one is the better choice?

So I decided to ask such a question to see if anybody knows what data type do giant applications use to store their time data?

  1. TIMESTAMP
  2. DATETIME
  3. UNIX TIMESTAMP
best_of_man
  • 367
  • 1
  • 3
  • 12

1 Answers1

1

Probably TIMESTAMP

TIMESTAMP is a likely choice because it records one time regardless of where the user is. It is essentially UTC on disk but is converted to/from the timezone of the machine looking at it. In conjunction with that, the servers (non necessarily the clients) are also set to UTC.

DATETIME, if your location honors DST ("Daylight S... Time"), has a missing hour once a year and an extra hour at another time of the year. This leads to various hiccups when it comes to comparing times, running cron jobs, etc.

(In the case of MySQL, UNIX_TIMESTAMP is a function, not a Datatype.)

In rare cases, you might keep "fractional seconds. For example TIMESTAMP(6) includes the time to microseconds. (However, do not believe that two events will never have the same time to the same microsecond; that is don't depend on it for UNIQUEness.)

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Can I have your comment about my question here: https://stackoverflow.com/questions/75377698/what-are-the-advantage-disadvantage-of-different-ways-of-storing-uuid-ids-within – best_of_man Feb 07 '23 at 19:06
  • @best_of_man - Please point me at Node.js's UUID (there are several types). Meanwhile, here are my comments on MySQL, plus there has been a lot fo discussion in stackoverflow.com. [_UUIDs_](http://mysql.rjweb.org/doc.php/uuid) – Rick James Feb 07 '23 at 19:10
  • Could you tell me which of those 3 ways you think is the best way for doing that? – best_of_man Feb 07 '23 at 19:26
  • This is the NodeJS library for UUID: https://www.npmjs.com/package/uuid – best_of_man Feb 07 '23 at 19:27
  • I also found this linbrary for UUID-V1-BINARY generation in NodeJS: https://www.npmjs.com/package/binary-uuid – best_of_man Feb 07 '23 at 19:41
  • 1
    Sounds like it is doing what MySQL 8's `UUID_TO_BIN(UUID(),1)` does. That is what my blog recommends, too. (Meanwhile, MariaDB 10.10 works another way.) This is good for locality of reference, somewhat good for space (16 bytes), and necessary if clients must create unique ids independently. – Rick James Feb 07 '23 at 19:52
  • 1
    But, if you can get an 8-byte `BIGINT AUTO_INCREMENT`, what would be smaller. Bottom line: I depends on your requirements. I have seen each being done by "the big guys". (I used to work for one of them.) – Rick James Feb 07 '23 at 19:54
  • @best_of_man - "of those 3 ways .. doing that" -- Spell out your requirements for "that", based on space, clients, uniqueness, etc. I have given you most of the tradeoffs. – Rick James Feb 07 '23 at 20:20
  • Should I use BINARY(16) type while I am creating my table schemas to store such a variable like this ` `? – best_of_man Feb 07 '23 at 20:47
  • 1
    Yes, that looks like a Type-1 uuid with the bits rearranged. (I'm assuming that is hex being stored in 16 bytes.) – Rick James Feb 07 '23 at 23:09