Given that languages and databases use different structures for storing date and time, what piece of code is responsible for transforming the date and time data structure when moving the date and time value from programming language to database and vice versa?

CodePudding user response：

Most systems store DATETIME as a floating point number. The value to the left of the decimal point is the number of days from day zero(0). Day zero can be anything. In Unix, day 0 is 1/1/1970. The year of Unix birth. However, this is not very intuitive to the average user. Oracle uses the actual year 0 and SQL Server uses 1/1/1900.

Convert the number 0 to a DATETIME and you'll get that systems Day 0.

The numbers to the right of the decimal point is the time. This can be recorded in milliseconds, microseconds, whatever granularity your system can handle.

The best part of this method is date math becomes very simple. Converting the number to the actual date and time is were all the complexity is involved.

Most programming languages will follow this same pattern.