How to format bigint field into a date in PostgreSQL?

Converting Milliseconds to Seconds with The Power of Division

If your bigint epoch timestamp is in milliseconds, conveniently convert it to seconds using simple division; after all, thousandths of a second rarely affect our day-to-day schedules:

-- Here, the magic number isn't 42, it’s 1000!
SELECT to_char(to_timestamp(your_bigint / 1000), 'DD/MM/YYYY HH24:MI:SS') AS formatted_date FROM your_table;


This will give you an accurate timestamp.

Type Casting: SQL's Costume Party

In some scenarios, you might need to type cast bigint into text before transforming it:

-- My ex used to say that I should always express my feelings, so here it goes:
SELECT to_timestamp(your_bigint::text, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM your_table;


Be mindful not to unexpectedly end up dating timestamptz (timestamp with timezone). It can be a complicated relationship, considering all those time zones. Use ::timestamp to get a neutral timestamp:

-- Dating is hard, especially when timezones are involved
SELECT to_timestamp(your_bigint)::timestamp AS formatted_date FROM your_table;


Outsmarting the Epoch Time Offsets

Sometimes your bigint could be an offset from the Unix epoch, a little adjustment may be required:

-- Trust is good, but validation is better
SELECT to_char(to_timestamp((your_bigint - offset) / 1000), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM your_table;


This minor adjustment helps to keep your dates accurate, accounting for those pesky epoch time offsets.

Validate or Vanquish: Avoiding Null and Formatting Errors

Once bigint's transformed into a date, don't forget to check your output. This is like your safety net beneath your data trapeze:

-- Nulls are like onions, they make my eyes water
SELECT formatted_date FROM your_table
WHERE to_timestamp(your_bigint) IS NOT NULL;


You would rather be safe than sorry with null values or bad date formatting.

Give Your Table a Makeover: Altering Column Types

Need a new look for your table? Update the column type with ALTER TABLE:

-- Extreme makeover: Table edition
ALTER TABLE your_table ALTER COLUMN your_column TYPE timestamp USING to_timestamp(your_bigint);


This keeps your valuable data snug and secure during the transformation.

The Lesser-known Wonders

Time Zones: Not Just for Jet Lag

In a nutshell, if you need precision with time zones, use the AT TIME ZONE clause:

-- Jetlag-proof your dates
SELECT to_char(to_timestamp(your_bigint) AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM your_table;


Transaction Control: Your Data's Bodyguard

Planning a large-scale data transformation? Use BEGIN and COMMIT for transaction control:

-- Commitment issues? BEGIN first, and only COMMIT when you're sure
BEGIN;
-- Your ALTER TABLE statement here
COMMIT;


Ranging the BigInt: Don't Fall Off the Edge

When your bigint exceeds certain ranges, use range checking to avoid conversion errors:

-- Right place, right bigint
SELECT to_char(to_timestamp(your_bigint), 'YYYY-MM-DD') AS formatted_date
FROM your_table
WHERE your_bigint BETWEEN '-2147483648' AND '2147483647';
