timezone

[AnnaGoodman1]

@nikolai-b One small thing – the program has correctly shifted the times forward by 1 hour in relation to daylight saving time coming in on Sunday. BUT it is still time stamping in GMT, i.e. pre clocks change the first run of the day was '06:00' [GMT] now it is '05:00' [GMT]. Which is correct in terms of the times (06:00 daylight saving time = 05:00 GMT), but labelling in GMT can easily cause confusion later on, as one must remember to add +1 hour during DST. Is it possible to have the times variably outputted in GMT / daylight saving time according to which is actually operational?

[AnnaGoodman1]

(since all the current data is just test data it would be fine for this discontinuity to just be introduced, no need to try to retrospectively fix it or anything)

[nikolai-b]

I'm only on my phone so can't check but adding AT TIME ZONE 'Europe/London' should give you the time in UTC+1 for time in summer time. So SELECT created_at AT TIME ZONE 'Europe/London' FROM journey_runs WHERE ....

[AnnaGoodman1]

Hi @nikolai-b , great thanks, but sorry I have had a go and think I will need more help on this. Is this code somewhere in the repo? Or do you mean in the request that I put in the command line?

At the moment I write the below, if you could edit it appropriately I will have a go

set "PGPASSWORD= [section removed] asker --csv -t -c "select runs.time, ltns.scheme_name, journey_runs.* from journey_runs join runs on journey_runs.run_id = runs.id JOIN ltns ON runs.ltn_id = ltns.id;" > "C:\Users\Anna\AnnaDesktop\test.csv"

[nikolai-b]

That gets all of them, you can scope it to just get the new ones

SELECT runs.time AT TIME ZONE 'Europe/London', ltns.scheme_name, journey_runs.* 
FROM journey_runs 
JOIN runs ON journey_runs.run_id = runs.id 
JOIN ltns ON runs.ltn_id = ltns.id
WHERE runs.id > 123456789;

with a WHERE clause. I've also added the AT TIME ZONE part. You may also notice that I've added a created_at to journey_runs so if you want that to be in the correct timezone you'd need:

SELECT runs.time AT TIME ZONE 'Europe/London' , ltns.scheme_name, journey_runs.*, journey_runs.created_at AT TIME ZONE 'Europe/London'
FROM journey_runs 
JOIN runs ON journey_runs.run_id = runs.id 
JOIN ltns ON runs.ltn_id = ltns.id
WHERE runs.id > 123456789;

[AnnaGoodman1]

perfect