The following query works fine, but does not return results with the most recent value at the top.

SELECT
    u.timestamp
FROM
    `app-68ccf.firestore_export.EventsHandle_raw_latest` AS u
ORDER BY
    u.timestamp DESC
LIMIT 50;

In my Google Cloud UI the top row timestamp value is 2025-03-13 18:29:02.109423 UTC

When I run the query the results returned are:

2025-03-13 18:29:02.109423
2025-03-10 18:29:02.109423
2025-03-14 18:29:02.109423

Expected results

2025-03-14 18:29:02.109423
2025-03-13 18:29:02.109423
2025-03-10 18:29:02.109423

It does not look like it is outputting the most recent value at the top even with the ORDER BY.

When I select the max u.timestamp it returns:

1.741896821962415E9

How can I get results sorted by most recent to least recent?

The following query works fine, but does not return results with the most recent value at the top.

SELECT
    u.timestamp
FROM
    `app-68ccf.firestore_export.EventsHandle_raw_latest` AS u
ORDER BY
    u.timestamp DESC
LIMIT 50;

In my Google Cloud UI the top row timestamp value is 2025-03-13 18:29:02.109423 UTC

When I run the query the results returned are:

2025-03-13 18:29:02.109423
2025-03-10 18:29:02.109423
2025-03-14 18:29:02.109423

Expected results

2025-03-14 18:29:02.109423
2025-03-13 18:29:02.109423
2025-03-10 18:29:02.109423

It does not look like it is outputting the most recent value at the top even with the ORDER BY.

When I select the max u.timestamp it returns:

1.741896821962415E9

How can I get results sorted by most recent to least recent?

• google-bigquery

• 1.741896821962415E9 is the same as 2025-03-13 20:13:42 UTC. To get a human-readable datetime. use SELECT TIMESTAMP_SECONDS(CAST(1.741896821962415E9 AS INT64)) or SELECT MAX(TIMESTAMP_SECONDS(CAST(timestamp AS INT64))) FROM `app-68ccf.firestore_export.EventsHandle_raw_latest` – mjruttenberg Commented Mar 15 at 16:05

1 Answer 1

You can also use this:

Your print of timestamp show it in milliseconds(float), you can use CAST and TIMESTAMP_MILLIS, to convert it to your desired output try using this updated query:

SELECT
   TIMESTAMP_MILLIS(CAST(u.timestamp * 1000 AS INT64)) AS ts
FROM
    `app-68ccf.firestore_export.EventsHandle_raw_latest` AS u
ORDER BY ts DESC
LIMIT 50;

If you don’t need the fraction of seconds (.109423) use this query:

SELECT 
  TIMESTAMP_MILLIS(CAST(u.timestamp AS INT64)) AS ts
FROM
  `app-68ccf.firestore_export.EventsHandle_raw_latest` AS u
ORDER BY ts DESC
LIMIT 50;