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’)))

PostgreSQL MSSQL Bigint to Date
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’);

PostgreSQL Bigint to Date
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 LocalTimePostgreSQL Bigint to Date2

 
 
Hope you find this article helpful.
 
 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s