In my previous article, I had discussed the conversion of bigint value to date time in both PostgreSQL and SQL Server. It raises several questions
1) Why it was converted into bigint in the first place?
2) How the bigint value is related to the date-time?
3) How to convert a date into bigint value?
Both BigInt and DateTime takes 8 bytes of storage. Moreover, DateTime looks more readable and understandable. However, bigint is the best choice to be a primary key as SQL engines cannot differentiate between DateTime values that are within a range. So, in case if we want the date column to be a primary key, we need to convert the value to bigint. Apart from that, integer values are always faster in retrieval than string or DateTime values. At least there will be some milliseconds difference.
The bigInt values that we see in the datetime columns are actually converted figures based on unix epoch time. Look at the below screenshot.
SELECT EXTRACT(EPOCH FROM NOW());
This will return a double-precision floating-point that represents the exact number of seconds, and milliseconds, that have passed since January 1, 1970. This means there is 1597737544 seconds difference between "1970-01-01 00:00:00" and "2020-08-18 09:59:04" (UTC Time).
Hope this answers the above-mentioned questions. However if you are curious to know what epoch time is, and why it is 1970-01-01 as the start date then continue reading the below.
Early Unix engineers picked that date discretionarily, in light of the fact that they expected to set a uniform date for the beginning of time, and New Year's Day, 1970, appeared to be generally helpful.
So the Unix epoch is midnight on January 1, 1970. It's time zero for any device that uses Unix.
