Question about using bigint to store time in database

[zongle luo]

Hi, I found that some tables in our project use bigint to store time. Most of the time, the bigint time is directly passed to the front end, but occasionally the back end will pay attention to the specific content of the time, and then use it in the code like `new Date(createTime)` to convert it.

Then I found that some people seem to be accustomed to storing time as bigint type. For example, some people may think that bigint is easier to handle time zone issues, and some people may be using mysql 5.5.x, and this version of mysql's DATETIME and TIMESTAMP do not seem to support millisecond values, etc.

So I would like to ask, can I add a TypeHandler for mybatis that can convert bigint to java.util.Date and java.time.LocalDateTime?

This allows people who use bigint to store time to use time in the code without having to create new Date.

[Iwao AVE!]

Hello,

You are encouraged to write such type handlers for your project or organization.

But they may not work for other projects/organizations because there can be various strategies to store date/time data into number type columns. I have seen data like 20210523100304 (i.e. yyyyMMddHHmmss) in a number type column.

Please let us know if you have any difficulty writing or using them.

Regards,

Iwao

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/859cee1b-2690-4ee2-8460-a862d3e97a9bn%40googlegroups.com.

[zongle luo]

Oh, I see. Maybe others save it like this (yyyyMMddHHmmss). Thank you. I will do it in our own project.

[Brian Pontarelli]

After about 20 years of battling with dates and times, I personally always store everything as an instant (milliseconds since Epoch UTC). It works for all types and is the simplest to convert and do math on. You often can do math on it in SQL if needed as well. And it makes time zones impossible to mess up, even when geopolitical forces cause them to change and the databases are kept up to date.

We have a library of custom TypeHandlers that manage all the conversions.

I highly recommend this approach. 

Sent from my iPhone

On Dec 29, 2024, at 10:48 PM, zongle luo <zongl...@gmail.com> wrote:

Oh, I see. Maybe others save it like this (yyyyMMddHHmmss). Thank you. I will do it in our own project.

To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/ff2934e6-13d1-43fc-a789-3778b6071930n%40googlegroups.com.

[zongle luo]

Got it, thank you. We also store milliseconds, but each time we manually convert from unix_timestamp to java.lang.Date. I am also working on customizing some TypeHandlers for our system.