PostgreSQL BigInt Value - Conversion to UTC and Local Times

Most of the ticketing systems by default use either MySQL or PostgreSQL and often stores the date-time of the DML events in Bigint format. When such tables data moved to SQL Server, the bigint value should be converted into date and time for readability and analysis purposes. 

This is not new, however, this article will help you in converting the bigint value to readable UTC (Coordinated Universal Time) and local time in both SQL Server as well as PostgreSQL.  

The below script works in SQL Server and converts the big integer value to UTC and local times.

DECLARE @BigIntVal BIGINT

SELECT @BigIntVal = 1574984632061

SELECT DATEADD(hh, +4, DATEADD(s, CONVERT(BIGINT, @BigIntVal) / 1000, CONVERT(DATETIME, '1-1-1970 00:00:00')))

The bigint value '1574984632061' is initially converted into UTC time and again is converted to Dubai local time by adding 4 hours to it.  You can add or subtract the hours based on your location/timezone.

DECLARE @BigIntVal BIGINT

SELECT @BigIntVal = 1574984632061

SELECT DATEADD(s, CONVERT(BIGINT, @BigIntVal) / 1000, CONVERT(DATETIME, '1-1-1970 00:00:00'))

The above will return the UTC date-time. Similarly, the following code in PostgreSQL will convert the big integer value to UTC date-time.

SELECT TO_CHAR(TO_TIMESTAMP(1574984632061/ 1000), 'YYYY-MM-DD HH24:MI:SS');

Add +4 hours to it to get the local (Dubai) date and time.

SELECT TO_CHAR(TO_TIMESTAMP(1574984632061/ 1000)

                         + INTERVAL '4 hour', 'YYYY-MM-DD HH24:MI:SS') as LocalTime

Hope you find this article helpful.