Score:1

Server

What data type do big applications use to store time data?

best_of_man

2/7/24, 5:41 AM

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?

TIMESTAMP
DATETIME
UNIX TIMESTAMP

1 + 0

mysql

time

date

cassandra

timestamp

Score:1

Server

Rick James

2/7/24, 6:30 AM

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

+ 10

best_of_man

2/7/24, 7:06 PM

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

Rick James

2/7/24, 7:10 PM

@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)

best_of_man

2/7/24, 7:26 PM

Could you tell me which of those 3 ways you think is the best way for doing that?

best_of_man

2/7/24, 7:27 PM

This is the NodeJS library for UUID: https://www.npmjs.com/package/uuid

best_of_man

2/7/24, 7:41 PM

I also found this linbrary for UUID-V1-BINARY generation in NodeJS: https://www.npmjs.com/package/binary-uuid

Rick James

2/7/24, 7:52 PM

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

2/7/24, 7:54 PM

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

2/7/24, 8:20 PM

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

best_of_man

2/7/24, 8:47 PM

Should I use BINARY(16) type while I am creating my table schemas to store such a variable like this `<Buffer 11 ed a7 28 25 a2 57 40 80 61 81 ed 9d 30 12 8e> `?

Rick James

2/7/24, 11:09 PM

Yes, that looks like a Type-1 uuid with the bits rearranged. (I'm assuming that is hex being stored in 16 bytes.)

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: What data type do big applications use to store time data?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.