Skip to content

Instantly share code, notes, and snippets.

@lukecav

lukecav/export-active-subscriptions.sql

Created February 19, 2026 17:54
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lukecav/5e2c7dd19347404b02fc000baf0db60c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lukecav/5e2c7dd19347404b02fc000baf0db60c to your computer and use it in GitHub Desktop.
Download ZIP
HPOS-compatible SQL query to export active subscriptions in WooCommerce including start date, next payment date and last order date
Raw
export-active-subscriptions.sql
SELECT
o.id AS subscription_id,
o.status AS subscription_status,
o.date_created_gmt AS date_created,
a.first_name,
a.last_name,
a.email,
o.total_amount,
o.currency,
last_order.meta_value AS last_order_date_created,
schedule_start.meta_value AS schedule_start,
schedule_next.meta_value AS schedule_next_payment
FROM
wp_wc_orders o
INNER JOIN
wp_wc_order_addresses a
ON o.id = a.order_id
LEFT JOIN
wp_wc_orders_meta last_order
ON o.id = last_order.order_id
AND last_order.meta_key = '_last_order_date_created'
LEFT JOIN
wp_wc_orders_meta schedule_start
ON o.id = schedule_start.order_id
AND schedule_start.meta_key = '_schedule_start'
LEFT JOIN
wp_wc_orders_meta schedule_next
ON o.id = schedule_next.order_id
AND schedule_next.meta_key = '_schedule_next_payment'
WHERE
o.type = 'shop_subscription'
AND o.status IN ('wc-active')
AND a.address_type = 'billing'
ORDER BY
o.id DESC;

Comments are disabled for this gist.