Time slider

[alexey-milovidov]

It can span for the whole time range (2022 .. now).

Workaround: it is already possible to simply add AND time BETWEEN ... AND ... in the WHERE condition in the query.

[Aleshere]

Hey Alexey, do we have data for 2024? It looks like this is up until 2023?

[alexey-milovidov]

@Aleshere yes, we have real-time data, updated every few seconds.
You need to add

AND time >= '2024-01-01' AND data_source = 'airplanes.live' to the WHERE condition.

Example: https://adsb.exposed/?zoom=6&lat=48.8503&lng=7.0313&query=5a39e027e487062e9944dc28315fa0e9

Last five minutes: https://adsb.exposed/?zoom=5&lat=39.6565&lng=-96.2402&query=56c83c59f309758aa839b61b6ecacd6c

[Aleshere]

Amazing, works like a charm. Thank you

[syonist]

If I only wanted to display flights between midnight and 7 am what can I add to the WHERE condition ? Looking in the tables I thought tohour(DateTime64) BETWEEN 0 AND 7 or hour BETWEEN 0 AND 7 might work. But neither did !

[alexey-milovidov]

@syonist, write toHour(time) BETWEEN 0 AND 7:

https://adsb.exposed/?zoom=5&lat=48.6313&lng=7.9321&query=ece72146892a323b5329eab3af09f667

Keep in mind that this is the time in UTC. The local time will be different.

You can get something resembling the local time with

time + INTERVAL lon / 15 HOUR AS local_time

https://adsb.exposed/?zoom=4&lat=43.4419&lng=-82.2930&query=61ea9941cfab051e0dde29fdb4ca6b36

[alexey-milovidov]

Red: night flights, Green: day flights, Blue: evening flights:

https://adsb.exposed/?zoom=4&lat=66.1708&lng=22.2566&query=92cc010e703e3bb32d853f98c6b073e0

[syonist]

Very many thanks, Tim On Sunday 13 October 2024 at 11:11:24 BST, Alexey Milovidov ***@***.***> wrote: Red: night flights, Green: day flights, Blue: evening flights: https://adsb.exposed/?zoom=4&lat=66.1708&lng=22.2566&query=92cc010e703e3bb32d853f98c6b073e0 — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>

[alexey-milovidov]

There is also limited direct access to the data:

clickhouse-client --host kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com --user website --secure

(it has limits on the number of scanned records and queries)

Note: to install clickhouse-client on Linux or Mac, do

curl https://clickhouse.com/ | sh

[blu3r4d0n]

Hi Alexey, is data still getting ingested into this clickhouse db? Just curious!

[alexey-milovidov]

Yes, it is updated in realtime. Type iddqd anywhere and select ClickHouse Cloud.

[blu3r4d0n]

Thanks, I ran the last five minutes query from above and selected the clickhouse cloud and it didn't seem to load any data which is what led me to believe that it's not current. It appears the latest data ingested is from 2024-12-31. if this is expected, that's fine! just ran max(date) on the planes_mercator table.

[blu3r4d0n]

Thanks, I ran the last five minutes query from above and selected the clickhouse cloud and it didn't seem to load any data which is what led me to believe that it's not current. It appears the latest data ingested is from 2024-12-31. if this is expected, that's fine! just ran max(date) on the planes_mercator table.

looks like whatever issue I was encountering has been resolved.

[alexey-milovidov]

I didn't set up monitoring for my cron jobs. I've revived one data source, but I still have to revive another.

[blu3r4d0n]

Thanks! appreciate the assistance.

[alexey-milovidov]

I've made the backend selector always visible.