Wrong time at DST change

[hans-helmut]

Hello Oli,

thanks for providing the code.

I would expect an offset of one hour during the DST change at Sunday, 2025-03-30 in Berlin, referring to your example:

=> SELECT event, time AT TIME ZONE 'Europe/Berlin' FROM get_sun_times(TIMESTAMP '2025-03-29', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
 event  |          timezone          
--------+----------------------------
 sunset | 2025-03-28 17:37:25.239133
(1 row)

=> SELECT event, time AT TIME ZONE 'Europe/Berlin' FROM get_sun_times(TIMESTAMP '2025-03-30', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
 event  |          timezone          
--------+----------------------------
 sunset | 2025-03-29 17:39:09.857757
(1 row)

=> SELECT event, time AT TIME ZONE 'Europe/Berlin' FROM get_sun_times(TIMESTAMP '2025-03-31', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
 event  |          timezone          
--------+----------------------------
 sunset | 2025-03-30 17:40:54.475374
(1 row)

=> SELECT event, time AT TIME ZONE 'Europe/Berlin' FROM get_sun_times(TIMESTAMP '2025-04-01', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
 event  |          timezone          
--------+----------------------------
 sunset | 2025-03-31 17:42:39.103856
(1 row)

Without conversation only the timezone changes, but not the value:

=> SELECT time,event from get_sun_times(TIMESTAMP '2025-03-30', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
             time              | event  
-------------------------------+--------
 2025-03-29 17:39:09.857757+01 | sunset
(1 row)

=> SELECT time,event from get_sun_times(TIMESTAMP '2025-03-31', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
             time              | event  
-------------------------------+--------
 2025-03-30 17:40:54.475374+02 | sunset
(1 row)

Due to my understanding the time-value is in local-time of timezone-offset.

[hans-helmut]

I would suggest the following patch:

--- suncalc.sql.orig	2025-04-13 22:23:12.587771765 +0200
+++ suncalc.sql	2025-04-17 17:46:33.121132352 +0200
@@ -15,7 +15,7 @@
 END;
 $$ LANGUAGE plpgsql IMMUTABLE;
 
--- Constant for Julian date '2020-01-01 12:00:00 UTC' --> https://en.wikipedia.org/wiki/Julian_day
+-- Constant for Julian date '2000-01-01 12:00:00 UTC' --> https://en.wikipedia.org/wiki/Julian_day
 CREATE OR REPLACE FUNCTION sc_j2000() RETURNS int AS
 $$
 BEGIN
@@ -300,11 +300,11 @@
     j_noon = sc_solar_transit_j(ds, m, l);
 
     event := 'solarNoon';
-    time := to_timestamp(sc_from_julian(j_noon)) AT TIME ZONE 'UTC';
+    time := to_timestamp(sc_from_julian(j_noon));
     RETURN NEXT;
 
     event := 'nadir';
-    time := to_timestamp(sc_from_julian(j_noon - 0.5)) AT TIME ZONE 'UTC';
+    time := to_timestamp(sc_from_julian(j_noon - 0.5));
     RETURN NEXT;
 
     FOR rec IN SELECT angle, start, "end"
@@ -327,13 +327,13 @@
             END;
 
             event := rec.start;
-            time := to_timestamp(rise_time) AT TIME ZONE 'UTC';
+            time := to_timestamp(rise_time);
 
             RETURN NEXT;
 
             event := rec."end";
-            time := to_timestamp(set_time) AT TIME ZONE 'UTC';
+            time := to_timestamp(set_time);
             RETURN NEXT;
         END LOOP;
 END ;

The reason is, that AT TIME ZONE 'UTC' removes the TZ, and later the local TZ is added:

=> select timestamp with time zone '2025-04-17 14:00:00+02' AT TIME ZONE 'UTC';
      timezone       
---------------------
 2025-04-17 12:00:00
(1 row)

=> select timestamp with time zone '2025-04-17 12:00:00';
      timestamptz       
------------------------
 2025-04-17 12:00:00+02
(1 row)

It now works better, despite of one day of:

SELECT time,event from get_sun_times(TIMESTAMP '2025-03-30', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
             time              | event  
-------------------------------+--------
 2025-03-29 18:39:09.857757+01 | sunset
(1 row)

=> SELECT time,event from get_sun_times(TIMESTAMP '2025-03-31', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
             time              | event  
-------------------------------+--------
 2025-03-30 19:40:54.475374+02 | sunset

This matches also roughly https://www.timeanddate.de/sonne/deutschland/berlin?month=3&year=2025, but with one day off.

To fix this you could query with noon time:

=> SELECT time,event from get_sun_times(TIMESTAMP WITH TIME ZONE '2025-03-29 12:00:00', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
             time              | event  
-------------------------------+--------
 2025-03-29 18:39:09.857757+01 | sunset
(1 row)

i=> SELECT time,event from get_sun_times(TIMESTAMP WITH TIME ZONE '2025-03-30 12:00:00', 52.5206828, 13.409282, 239) WHERE event = 'sunset';
             time              | event  
-------------------------------+--------
 2025-03-30 19:40:54.475374+02 | sunset

[iisfaq]

Thanks for the patch file it took a bit for me to get it working on Windows but got there in the end.

Using your example seems to be working fine for sunrise/sunset, but the golden hour might be wrong like sunrise/sunset

SELECT * from get_sun_times(TIMESTAMP WITH TIME ZONE '2025-07-01 12:00:00', -43.5, 172.6,0)

Verified against https://sunrisesunset.io/nz/canterbury-region/christchurch/

+---------------+-------------------------------+
| Event         | Time                          |
+---------------+-------------------------------+
| solarNoon     | 18/04/2025 12:30:07.867281 pm  |
| nadir         | 18/04/2025 12:30:07.867281 am  |
| sunrise       | 18/04/2025 7:06:41.385619 am   |
| sunset        | 18/04/2025 5:53:34.348944 pm   |
| sunriseEnd    | 18/04/2025 7:09:43.351278 am   |
| sunsetStart   | 18/04/2025 5:50:32.383284 pm   |
| dawn          | 18/04/2025 6:37:36.055315 am   |
| dusk          | 18/04/2025 6:22:39.679247 pm   |
| nauticalDawn  | 18/04/2025 6:04:18.200651 am   |
| nauticalDusk  | 18/04/2025 6:55:57.533911 pm   |
| nightEnd      | 18/04/2025 5:31:12.095110 am   |
| night         | 18/04/2025 7:29:03.639453 pm   |
| goldenHourEnd | 18/04/2025 7:46:12.529627 am   |
| goldenHour    | 18/04/2025 5:14:03.204935 pm   |
+---------------+-------------------------------+

Cheers

[hans-helmut]

Hm, it matches roughly the values given on https://meteogram.org/sun/new-zealand/christchurch/ , but I have not checked the calculation, and I have not the knowledge to do.
The golden hour in the morning goes from sunrise to goldenHourEnd and in the evening form goldenHour to sunset.

[rduivenvoorde]

Working with timestamptz values in a database, without this patch I was one hour off.

So: THANKS for this patch! :-)

[kengruven]

I concur that removing AT TIME ZONE 'UTC' in these 4 places fixes the results for the few cases that I tried.