Making sense of 'event_timestamp' numbers

Question

I want to find the exact time stamp for an event but the numbers in the 'event_timestamp' column doesn't seem to make sense:

event_timestamp

I've been googling for some days now and I can't seem to find an answer to my problem. It's making me think if I'm asking the wrong question or if I don't actually understand what a timestamp is in this context.

I just want to be able to convert that bunch of numbers into time in seconds, minutes, and hours so I know exactly when an event got triggered by the user.

(I want the time because I want to check what other things triggered X minutes before that event got triggered)

Thanks in advance,

Try [`TIMESTAMP_MICROS`](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_micros) — Elliott Brossard, Jan 20 '19 at 23:23
That seems to have done the trick. Thanks! Curious though, it gave me this "2019-01-19 22:54:23.979 UTC" ... what is that 979 for at the end? — Cliff Kamarga, Jan 21 '19 at 00:08

Alex Riquelme · Accepted Answer · 2019-01-21T21:37:46.410

4

As @elliott-brossard pointed, using TIMESTAMP_MICROS will convert the UNIX Epoch time into human readable time.

Let me try to explain what does that number means. UNIX Epoch time it is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970. Unix time is a single signed integer which increments every second, without requiring the calculations to determine year, month, day of month, hour and minute required for intelligibility to humans.

So, because the number of digits that the number that you shared has, it is in microseconds, so the calculation should be: 1/1,000,000 second. As described above, the number of seconds elapsed since 1 January 1970 is 1547938463. There are multiples ways to transform that number into human readable format, such as the way that @elliott-brossard described, also, every program language has libraries to convert that number and there are multiple web pages to do it.

edited Jan 21 '19 at 21:37

answered Jan 21 '19 at 09:44

Alex Riquelme

1,475
7
14

Ooooooh I see. That's very interesting. Thanks for explaining that. Also thanks to Elliott as well for the help =) Since I'm new here, I know I can tick your answer to say it helped me. Can I do something similar to Elliott's comment to show that he helped me as well? – Cliff Kamarga Jan 21 '19 at 19:58
@CliffKamarga You can up-vote comments by clicking the up arrow right next to them. However, you need 15+ of reputation to do it. – Alex Riquelme Jan 21 '19 at 20:17
Oh ok. I will keep that mind for the future. Thanks :) – Cliff Kamarga Jan 21 '19 at 20:35

Making sense of 'event_timestamp' numbers

1 Answers1