database - ISOdate/POSIXct vs Milliseconds -

this more of thinking question. have been working around different time/date formats, , noticed seems preferred store date/time objects variables unique classes (like isodate or posixct) in databases (like mongo, mysql, postegen).

i why 1 want convert such format when analyzing data, wondering what's advantage when store in format in data-base?

do these formats tend take less space conventional numbers? can't seem find answer online.

for arguments sake let's talk simple date type (just date, no time or time zone) - such date type in mysql.

say stored string of 2014-12-31. what's 1 day later? human, it's easy come answer 2015-01-01, computer needs have algorithms programmed in.

while these types might expose apis have algorithms dealing calendar math, under hood store information whole number of days since starting date (which called "epoch"). 2014-12-31 stored 16701. computer can efficiently add 1 16702 next day.

this makes easier sort. sure, in yyyy-mm-dd format, lexicographical sort order preserved, still takes more processing power sort strings integers. also, date might formatted other cultures when represented string, such in mm/dd/yyyy or dd/mm/yyyy format, not lexicographically sortable. if through thousands of dates table , query where or order by clause, database needs able efficiently sort values, , integer sorting faster analyzing strings.

and yes - tend take less physical storage space well.

the same principles apply when date , time both present, , have contend precision of time value (seconds, milliseconds, nanoseconds, etc.)