I am getting invalid results for Server in Christchurch New Zealand.

[iisfaq]

I have a small issue running your code

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' as UTC, CURRENT_TIMESTAMP AT TIME ZONE 'Pacific/Auckland' as Auckland

utc, auckland
14/04/2025 12:22:59.629772 AM AD,14/04/2025 12:22:59.629772 PM AD

These are correct.

But when I run this

SELECT event, time FROM public.get_sun_times(
CURRENT_TIMESTAMP AT TIME ZONE 'pacific/Auckland',
-43.53,
172.63,
0
);

event,time
solarNoon,14/04/2025 12:30:57.144061 AM+12 AD
nadir,13/04/2025 12:30:57.144061 PM+12 AD
sunrise,13/04/2025 7:01:56.278575 PM+12 AD
sunset,14/04/2025 5:59:58.009546 AM+12 AD
sunriseEnd,13/04/2025 7:04:56.856433 PM+12 AD
sunsetStart,14/04/2025 5:56:57.431688 AM+12 AD
dawn,13/04/2025 6:33:01.216090 PM+12 AD
dusk,14/04/2025 6:28:53.072032 AM+12 AD
nauticalDawn,13/04/2025 5:59:48.685436 PM+12 AD
nauticalDusk,14/04/2025 7:02:05.602685 AM+12 AD
nightEnd,13/04/2025 5:26:41.082216 PM+12 AD
night,14/04/2025 7:35:13.205905 AM+12 AD
goldenHourEnd,13/04/2025 7:41:04.123414 PM+12 AD
goldenHour,14/04/2025 5:20:50.164707 AM+12 AD

The sunrise should be at 7:01am on the 14th of April 2025 +12:00

https://www.timeanddate.com/sun/new-zealand/christchurch

What am I doing wrong?

[olithissen]

Hi @iisfaq,

it looks like you're missing a time zone conversion in your projection part. As suncalc_postgres is just a port of the original at https://github.com/mourner/suncalc you can use it as a reference: 14/04/2025 12:22:59.629772 AM UTC translates to 1744590179.

suncalc_postgres

SELECT
  event,
  EXTRACT(EPOCH FROM time::timestamp without time zone) AS unix_epoch, --1744570916
  time AT TIME ZONE 'Pacific/Auckland' AS auckland -- 2025-04-14 07:01:56
FROM
  public.get_sun_times(to_timestamp(1744590179), -43.53, 172.63, 0)
WHERE
  event = 'sunrise';

suncalc.js

const SunCalc = require('./suncalc');
const date = new Date(1744590179 * 1000);
const sunrise = SunCalc.getTimes(date, -43.53, 172.63, 0).sunrise;

console.log(`(UTC): ${sunrise.toISOString()}`); 
// --> 2025-04-13T19:01:56.278Z
console.log(`(Pacific/Auckland): ${sunrise.toLocaleString('en-NZ', { timeZone: 'Pacific/Auckland' })}`); 
// --> 14/04/2025, 7:01:56 am
console.log(`(Unix Time): ${Math.floor(sunrise.getTime() / 1000)}`); 
// --> 1744570916

But I have to agree: It feels like there is still something off. It was quite hard to test this from 'Europe/Berlin'. In retrospect it would have been better if the functions were to return EPOCH instead of timestamp with time zone.

Let me know if this works for you.

Oli

[hans-helmut]

Hello @iisfaq, could you please test the proposed patch: #3 (comment)