Have Geek Brain

Storing date and time in epoch vs native datetime format in database

For most of my tasks, it is much easier for me to work with date and time in epoch format:  

It is trivial to calculate the span of time or to determine if some event happened before or after another.I don't need to deal with timezone issues if the data comes from different geographic sources, in the case of scripting languages, what I usually get from the database when I query the column with datatype is the string that I need to parse in order to work with it.

The list could go on, but for me, my portable code is sufficient to strip out the underlying database date and time format and store the date and time as an integer. What do you guys think?

+2


source to share


3 answers


You should be able to fix the timezone problem using UTC time. I know C has a function to convert epoch time to UTC. I couldn't find another way to go, which confuses me.



0


source


I think this is how I use the appropriate model objects and methods in MVC, these dates and times are becoming trivial. Calculations depend on the date and time library. There are very few times when I had to write my own and in fact in those days I lost sight of the fact that the functionality was already in the library. If you are using PHP grab the date and time library from Zend Framework. Once you've developed your model, reuse it. Then you don't care how the date and time are stored in the DB.



0


source


The main reason for using a database type DATETIME

is because POSIX time_t

has a very limited range; you cannot store dates outside of the infamous Y2038 or earlier than 1904. Because many databases need to store historical or future dates, there is a full range and is usually portable DATETIME

; if in fact the POSIX era is more useful to you then go ahead and use them.

0


source






More articles:


All Articles