![\"Five](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/zeitstempel-umrechnen-en.png?v=20260925\")
Five Formats That Meet in One Stack<\/h2>\n
All of them count from the same zero point: midnight on 1 January 1970, in UTC. What differs is the unit and the packaging.<\/p>\n
| Format<\/th>\n | Example<\/th>\n | Where it appears<\/th>\n<\/tr>\n<\/thead>\n |
|---|---|---|
| Seconds<\/td>\n | 1790465400<\/td>\n | Meta CAPI event_time, most REST APIs<\/td>\n<\/tr>\n |
| Milliseconds<\/td>\n | 1790465400000<\/td>\n | Date.now(), gtm.start, most JavaScript<\/td>\n<\/tr>\n |
| Microseconds<\/td>\n | 1790465400000000<\/td>\n | GA4 BigQuery event_timestamp<\/td>\n<\/tr>\n |
| ISO 8601<\/td>\n | 2026-09-27T01:30:00+02:00<\/td>\n | Logs, Search Console API, most exports<\/td>\n<\/tr>\n |
| Date only<\/td>\n | 20260927<\/td>\n | GA4 event_date, BigQuery table suffix<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n The first three are unambiguous: they are a number of units since a fixed point, and they contain no time zone because they do not need one. The fourth is unambiguous as long as it carries an offset – the The fifth is different in kind. A date without a time is not a moment but a range of twenty-four hours, and which twenty-four hours depends on a time zone that is not written anywhere in the value.<\/p>\n For a number found in a log, in an export or in a payload, the length settles it.<\/p>\n Two mistakes come out of ignoring this, and both produce a result that looks plausible. Reading milliseconds as seconds moves the date to the year 58 707 – which is obviously wrong and therefore harmless. Reading seconds as milliseconds moves it to 21 January 1970, which is not obviously wrong at all and appears in a report as a tiny bar at the far left of every chart.<\/p>\n A guard clause is three lines and worth having wherever a value arrives from outside.<\/p>\n The GA4 BigQuery export carries both a moment and a date, and they use different conventions.<\/p>\n This is not a bug and it cannot be switched off. It is the reason two queries over the same table can return different daily totals, and the difference is always the same size: the events of the first one or two hours of each day.<\/p>\n The rule that follows is short. Grouping by day uses either Three functions cover every case, and each takes the unit in its name.<\/p>\n The distinction between In the other direction, a string becomes a timestamp only with a stated format, and the format string is where an offset is either read or invented.<\/p>\n Two receiving systems check the value rather than just storing it, and both fail in a way that is easy to miss.<\/p>\n The Meta Conversions API accepts an A timestamp in the future is the other half of the same rule, and it happens more often than the past one: a server whose clock runs a few minutes fast produces events that the platform declines. Which means a failing conversion upload is worth investigating as a clock problem before it is investigated as a data problem.<\/p>\n Offline conversion uploads to advertising platforms usually want a local time with an explicit offset rather than an epoch value.<\/p>\n The offset is not decoration. Without it the platform applies the account time zone, and an account configured in a different zone from the shop shifts every conversion by the difference – which shows up as conversions attributed to the wrong day and, at a month boundary, to the wrong month.<\/p>\n Everything above is a conversion. These two are losses, and the only remedy is not to create them.<\/p>\n The first is a local time without an offset. The second is the changeover night. When the clocks go back, the hour between two and three in the morning happens twice, and a local time inside it is genuinely ambiguous even with the zone known. An offset resolves it, because the two passes have different offsets. A zone name alone does not.<\/p>\n Both disappear if values are stored as UTC and converted only for display. That is a one-line rule that sounds obvious and is broken constantly, because a local time is what a person wants to read – and the fix is to convert at the point of reading rather than at the point of writing.<\/p>\n Most likely nanoseconds since the epoch. This format is used by OpenTelemetry, for example, and by the UnixNano function in Go; in a tracking stack it mainly turns up in server and log data. The guard clause rightly rejects it instead of guessing, and it can be extended by one line for 19 digits with the divisor Today, yes, but only just. JavaScript stores numbers as double-precision floating-point values, which represent integers exactly only up to 9,007,199,254,740,991 ( Nanoseconds, by contrast, lie far beyond it. A 19-digit value loses its last digits when read with That removes the difference between event_date and the timestamp but moves the problem somewhere else. Every daily report in GA4 then starts at midnight UTC, which for a shop in Berlin means one in the morning in winter and two in the morning in summer. A purchase at half past midnight counts towards the previous day, and daily figures no longer match the till, the inventory system and the ad accounts that work in local time.<\/p>\n Then there is the change itself. A new reporting time zone applies only to data from the moment of the change; earlier days stay as they were. The day of the change is therefore shorter or longer than twenty-four hours, and a time series spanning the change puts Berlin days next to UTC days.<\/p>\n The rule from the article holds up better: the property keeps the zone the business works in, and every query commits to one convention. Grouping by Berlin days in BigQuery then has the same day boundaries as the interface, and grouping by UTC is a deliberate choice of a different day.<\/p>\n With two checks. On the server itself, If the offset is in the range of minutes, it disappears as soon as synchronisation with a time server is running. Events already sent with the wrong time and rejected then have to be sent again with a corrected timestamp, as long as they are still inside the seven-day window.<\/p>\n<\/div>\n |