3 ms·
The data was retrieved from the public HN dataset on BigQuery: https://news.ycombinator.com/item?id=40644563 https://news.ycombinator.com/item?id=40644563
by minimaxir 2y ago
The data was retrieved from the public HN dataset on BigQuery: https://news.ycombinator.com/item?id=40644563 https://news.ycombinator.com/item?id=40644563
SELECT
id,
title,
url,
FORMAT_TIMESTAMP("%Y-%m-%d %H:%m:%S", timestamp, "America/New_York") AS
submission_datetime,
score,
descendants as comments
FROM
`bigquery-public-data.hacker_news.full`
WHERE
type = "story"
AND REGEXP_CONTAINS(url, r"daringfireball\.net")
ORDER BY submission_datetime
- someothherguyy 2y agoShould be %H:%i:%S?
- minimaxir 2y ago%i is not an option. https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time https://cloud.google.com/bigquery/docs/reference/standard-sq... Looking it up it's only used in really old DBs. ...but that table reveals I should have used %M instead of %m. Whoops! Although in this particular case it doesn't make a difference. And apparently I can do "%F %X" instead of the whole string.
- someothherguyy 2y agoYeah, just looked wrong, didn't check the docs