PostgreSQL Converting Epoch (date long) to Formatted Date (vice versa)

Epoch : The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but ‘epoch’ is often used as a synonym for ‘Unix time’. Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038). Source

Converting Formatted Date (yyyymmdd hh:mm:ss) to epoch in PostgreSQL :

select extract(epoch from to_timestamp('2018-08-06 13:30:37', 'YYYY-MM-DD HH24:MI:SS')) * 1000;

Result :


Converting epoh to Formatted Date (yyyymmdd hh:mm:ss) in PostgreSQL :

select to_char( to_timestamp(1533537037000 / 1000), 'YYYY-MM-DD HH24:MI:SS');

Result :

2018-08-06 13:30:37

Another Example :

I would like to find the data from particular table which have created_date epoch less than particular formatted date :

select code, shift_name, to_char( to_timestamp(created_date / 1000), 'YYYY-MM-DD HH24:MI:SS')  
from internal_shift where created_date < (
	select extract(epoch from to_timestamp('2018-08-06 11:00:37', 'YYYY-MM-DD HH24:MI:SS')) * 1000

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>