Supabase•4d ago

MisMatch Timezones

Hey, I need help configuring my Edge Functions so they use the user’s local timezone instead of UTC for my scheduled reminders (24h and 1h before a booking). Right now everything is firing based on UTC+00, which breaks the logic when the provider or client is in another timezone. I need a proper way to: Detect the user’s timezone (stored in DB or passed in request — either works). Run the Edge Function’s time comparisons using that timezone, not the default UTC of Deno. Make sure the scheduled reminders (24h / 1h before) are calculated correctly without relying on messy manual offsets.

9 Replies

ibrahim•4d ago

I think what you might want is a combination of cron job + database function that then calls and edge function and it passes in those time values that are 1 hours/24 hour before store everything in UTC

$ZairouOP•4d ago

Could you elaborate please? or if you are free I would be glad to talk a bit more

ibrahim•4d ago

$ZairouOP•4d ago

Oh database function? It can't be handled with supabase edge function?

ibrahim•4d ago

    org_record RECORD;
    http_response  net.http_response; 
    job_start_time TIME WITH TIME ZONE;
  BEGIN
    -- Capture the time at the start of the job
    job_start_time := CURRENT_TIME;
    RAISE NOTICE 'Starting bill due alerts processing at %', job_start_time;

    -- Use the captured time for all organizations
    FOR org_record IN
      SELECT
        organisation_id,
        jsonb_agg(
          jsonb_build_object(
            'invoice_id', invoice_id,
            'alert_type', alert_type,
            'invoice_number', invoice_number
          )
        ) as alert_results,
        COUNT(*) as result_count
      FROM public.find_bills_due_for_alerts(job_start_time)
      GROUP BY organisation_id
    LOOP
      RAISE NOTICE 'Processing % bill due alerts for organisation %', org_record.result_count, org_record.organisation_id;

      -- Call edge function using Vault secrets
      SELECT * INTO http_response FROM
        net.http_post(
          url := (SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name = 'project_url') || '/functions/v1/create-alert',
          headers := jsonb_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || (SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name = 'service_role_key')
          ),
          body := jsonb_build_object(
            'organisation_id', org_record.organisation_id,
            'alert_results', org_record.alert_results
          )
        );

      -- Log the result
      IF http_response.status_code != 200 THEN
        RAISE WARNING 'Failed to send bill due alerts for organisation %: Status %, Response: %',
          org_record.organisation_id, http_response.status_code, http_response.body;
      ELSE
        RAISE NOTICE 'Successfully sent % bill due alerts for organisation %', org_record.result_count, org_record.organisation_id;
      END IF;

    END LOOP;
  END;

    org_record RECORD;
    http_response  net.http_response; 
    job_start_time TIME WITH TIME ZONE;
  BEGIN
    -- Capture the time at the start of the job
    job_start_time := CURRENT_TIME;
    RAISE NOTICE 'Starting bill due alerts processing at %', job_start_time;

    -- Use the captured time for all organizations
    FOR org_record IN
      SELECT
        organisation_id,
        jsonb_agg(
          jsonb_build_object(
            'invoice_id', invoice_id,
            'alert_type', alert_type,
            'invoice_number', invoice_number
          )
        ) as alert_results,
        COUNT(*) as result_count
      FROM public.find_bills_due_for_alerts(job_start_time)
      GROUP BY organisation_id
    LOOP
      RAISE NOTICE 'Processing % bill due alerts for organisation %', org_record.result_count, org_record.organisation_id;

      -- Call edge function using Vault secrets
      SELECT * INTO http_response FROM
        net.http_post(
          url := (SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name = 'project_url') || '/functions/v1/create-alert',
          headers := jsonb_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || (SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name = 'service_role_key')
          ),
          body := jsonb_build_object(
            'organisation_id', org_record.organisation_id,
            'alert_results', org_record.alert_results
          )
        );

      -- Log the result
      IF http_response.status_code != 200 THEN
        RAISE WARNING 'Failed to send bill due alerts for organisation %: Status %, Response: %',
          org_record.organisation_id, http_response.status_code, http_response.body;
      ELSE
        RAISE NOTICE 'Successfully sent % bill due alerts for organisation %', org_record.result_count, org_record.organisation_id;
      END IF;

    END LOOP;
  END;

this is sample i code i have lying about (might have to change it for your own case) personally it would make sense to do store the user timezon in the database and doing cron job -> database function -> edge function seems better than doing cron job ->edge function ->database function because edge functions might timeout in a query or something it makes more sense to me to do all the heavy data operations first and then pass that into an edge function to send out emails

$ZairouOP•4d ago

Oh so every X minutes, cron runs a DB function that finds booking where local time is either 24h or 1h before the booking, groups them, and calls my edge function with the payload which will be simple -send mail- if true

ibrahim•4d ago

yeah that is what i would do (others might have better ideas though)

$ZairouOP•4d ago

Only you have answered for now, what means its the best answer Thank you ibrahim,

ibrahim•4d ago

no worries!

Gaming

Programming

MisMatch Timezones

Did you find this page helpful?