Datetime vs Timestamp datatype in databases - Which one is better and when?

Подписаться 120 тыс.

Просмотров 13 тыс.

50% 1

System Design for SDE-2 and above: arpitbhayani.m...
System Design for Beginners: arpitbhayani.m...
Redis Internals: arpitbhayani.m...
Build Your Own Redis / DNS / BitTorrent / SQLite - with CodeCrafters.
Sign up and get 40% off - app.codecrafte...
In the video, I explained the key differences between the "datetime" and "timestamp" data types commonly used in databases. Datetime stores both date and time, covering a vast range, while timestamp stores elapsed seconds since 1970 as an integer, with a limited date range. Datetime is ideal for user-specific and time zone-sensitive data, offering native date functions, whereas timestamp is efficient for recording system time events due to its lightweight nature. Consider the specific use case to choose the appropriate data type.
Recommended videos and playlists
If you liked this video, you will find the following videos and playlists helpful
System Design: • PostgreSQL connection ...
Designing Microservices: • Advantages of adopting...
Database Engineering: • How nested loop, hash,...
Concurrency In-depth: • How to write efficient...
Research paper dissections: • The Google File System...
Outage Dissections: • Dissecting GitHub Outa...
Hash Table Internals: • Internal Structure of ...
Bittorrent Internals: • Introduction to BitTor...
Things you will find amusing
Knowledge Base: arpitbhayani.m...
Bookshelf: arpitbhayani.m...
Papershelf: arpitbhayani.m...
Other socials
I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
LinkedIn: / arpitbhayani
Twitter: / arpit_bhayani
Weekly Newsletter: arpit.substack...
Thank you for watching and supporting! it means a ton.
I am on a mission to bring out the best engineering stories from around the world and make you all fall in
love with engineering. If you resonate with this then follow along, I always keep it no-fluff.

Опубликовано:

8 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 19

@manishbolbanda9872 Год назад

much needed. i always used to read about their differences but i never actually read it. Thanks for the video Arpit.

@mayank2662 Год назад

Great explanation Arpit!!

@nimitkanani1691 Год назад

Great video and an amazing simple explanation as always, unique choice of topic, this topic is not discussed very actively. I now realize that in the product I am working on, developers have used timestamps at some places where ideally datetime should be used, me being one of the contributors to that. 😅😅

@santoshbhatnagar2155 Год назад

Hi Nithin, what problems have you observed when timestamp was used instead of date time?

@gihanpunarji6192 Месяц назад

Wow. Thank you

@shishirchaurasiya7374 Год назад

Amazing Information deleivered Loved it❤

@raj_kundalia Год назад

thanks, never thought about this!

@VigneshkumarChinnachamyM 11 месяцев назад

Thank you for the nice explanation. I'm curious why Date supports only the years beginning 1000 and not 0001?

@antrapurohit8010 Год назад

So timestamp calculates the seconds elapsed from 1970-01-01. What if we/sql moves this date to some recent one? I.e 2000-01-01. Given the fact that Application uses db, wants to store records after 2000-01-01 ? This way, we don't exhaust in 2038 ?

@suryachinnu7756 Год назад

Coolest explanation….Thanks Arpit👏🏻🤝

@nirutgupta5894 Год назад

Just wondering what if we have some db using timestamp.. knowing the fact that it will not stay after 2038 makes me think to change the db schema and update all rows to use datetime instead. (Very optimistic about the application that it will stay for 15 years)

@dsysd-dev Год назад

I have a similar question about UUIDs and int primary keys ? is UUID bad because those random IDs may result in buffer cache miss or something while fetching the data

@hc90919 Год назад

Don’t you think, DB standardization groups are going to reset the timezone range beyond 2038 when we come closer to that range ?

@nirutgupta5894 Год назад

Yes they have to some how

@bhavneetsingh5864 Год назад

Awesome video man.. Also to clear do we really need DateTime nowdays because cases you mentioned also needs to be associated with timezone

@AsliEngineering Год назад

we do need it because we will exhaust the timestamp by 2038, and if the use case requires us to store date time beyond 2038, then a definite yes.

@AbhijatyaGupta99 5 месяцев назад

Can't we just use long instead of int for timestamps and enjoy using them till April 11, 2262?

@AsliEngineering 5 месяцев назад

Yes. That's the plan post 2038. But that is not straightforward for database to implement. That is a fundamental change at the database level. Also, imagine a company would need to forcefully update their version to the latest on (once it is shipped with 8B width). Also, this additional 4B would be taking up space on storage, both table data and index. This will lead to performance degradation.