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

Build Your Own Redis / DNS / BitTorrent / SQLite - with CodeCrafters.

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

# Things you will find amusing

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

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.

[manishbolbanda]

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

[shishirchaurasiya]

Amazing Information deleivered
Loved it❤

[antrapurohit]

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 ?

[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

[nimitkanani]

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

[VigneshkumarChinnachamyM]

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

[nirutgupta]

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)

[hc]

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

[bhavneetsingh]

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

[AbhijatyaGupta]

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