Calculate number of days since from timestamp I have a column in my database table containing timestamp.

SELECT id, millisecs, to_timestamp(millisecs) AS date_time FROM my_table;
id      | millisecs  |       date_time
--------+------------+------------------------
 126952 | 1494161691 | 2017-05-07 13:54:51+01
 126952 | 1494161692 | 2017-05-07 13:54:52+01
 126952 | 1494161693 | 2017-05-07 13:54:53+01
 126952 | 1494161694 | 2017-05-07 13:54:54+01
 126952 | 1494161695 | 2017-05-07 13:54:55+01


Then I want to calculate the number of days since '1899/12/30' using either the millisecs or date_time columns. As my table has large number of rows, doing this with a python function in notebook kills the kernel after sometime.
You can do this:

SELECT DATE_PART('DAY', date_time - '1899-12-31 00:00:00+01'::TIMESTAMPTZ) 
FROM date_test;


(typo in previous answer - had 1899-01-01 instead of 1899-12-31.)

See the dbfiddle here:

Just also note in the 2nd last field in the fiddle (added):

SELECT TIMESTAMP 'epoch' + 1494161691 * INTERVAL '1 second';


Result:

epoch_date
2017-05-07 12:54:51


Which is the date in the main question.

So, it would appear that your field called millisec is, in fact, seconds from 1970 (aka UNIXTIME or similar in other RDBMS). Check out this link. Unless you are storing milliseconds, then I would strongly suggest that you shouldn't call it millisec.

Create and populate table:

CREATE TABLE date_test
(
  id INTEGER NOT NULL,
  millisecs INTEGER NOT NULL,
  date_time TIMESTAMPTZ NOT NULL
);

INSERT INTO date_test 
VALUES
( 126952 , 1494161691 , '2017-05-07 13:54:51+01'),
( 126952 , 1494161692 , '2017-05-07 13:54:52+01'),
( 126952 , 1494161693 , '2017-05-07 13:54:53+01'),
( 126952 , 1494161694 , '2017-05-07 13:54:54+01'),
( 126952 , 1494161695 , '2017-05-07 13:54:55+01');


Then run the query above - result:

date_part
    42861
    42861
    42861
    42861
    42861


Links here and here helped!