Convert UTC bigint to timestamp

I have a field 'event_timestamp' that is supposedly in UTC time but is currently a bigint field not a timestamp. Documentation for this field:

> The time (in microseconds, UTC) when the event was logged on the client.

Here is an example value from this field: 1681149457712377

I know from a corresponding field event_date that the timestamp should correspond to date 2023-04-10

I need to calculate the time delta between events and so need to convert the bigint field event_timestamp to an actual timestamp. I read this SO post and tried the following:

select to_date(1681149457712377::text, 'YYYYMMDD')
ERROR: value for "DD" in source string is out of range
  Detail: Value must be in the range -2147483648 to 2147483647.

Then tried:

select trunc(TIMESTAMP 'epoch' + 1681149457712377 / 1000 * INTERVAL '1 second')
55243-07-04 # nonsensical date

But what I really want is to calculate the time difference in seconds between two of these bigint timestamps e.g. whats the time difference in seconds between 1681149457712377 and 1681101230813726, two example values from my event table. How can I do this?

First, you need to subtract one bigint from the other. This will give you their difference in microseconds.

One microsecond is 1e-6 seconds. So, then you need to multiply that difference by 1e-6 to convert it to seconds.

Take a note that this timestamp is in microseconds (not milliseconds), so you need to divide by 1,000,000 not by 1,000.

I don't know what SQL you use, but many of them have function to_timestamp that can do this conversion for you.

For example in snowflakeSQL you can use

select to_timestamp(1681149457712377, 6)

where 6 means "precision" - here it means microseconds (10^-6)

Some sqls may not have this function built in, or may only allow seconds. Then you need to manually divide the timestamp.