nlqdb

Solve · Analysts and PMs

How do I calculate the time between two dates in SQL?

If you need the time between two dates — days from signup to first order, hours to resolve a ticket — ask in plain English. nlqdb compiles the date arithmetic (`end - start` for an interval, `EXTRACT(EPOCH FROM (end - start))/86400` for whole days), runs it in Postgres, and shows the SQL so the unit is the one you meant.

Measuring elapsed time — days from signup to first order, hours to resolve a support ticket, the lag between two events — is the everyday analytical question that's fiddly to get exactly right. Subtracting two `date` values gives an integer number of days, but subtracting two `timestamp` values gives an `interval`, and that's where people trip: reach for `age(end, start)` and Postgres normalizes the gap into months — a 40-day span comes back as '1 mon 9 days', so `EXTRACT(DAY FROM age(…))` reads 9, not 40. The reliable whole-day count is `EXTRACT(EPOCH FROM (end - start)) / 86400`, and averaging durations means averaging intervals, not the raw dates.

The snippet that solves it.

> days between each signup and that customer's first order

What nlqdb does for this

  • Ask 'days between each signup and that customer's first order'; nlqdb compiles the date subtraction and runs it in Postgres.
  • For a plain day count it uses `EXTRACT(EPOCH FROM (end - start)) / 86400`, not `EXTRACT(DAY FROM age(…))`, whose month-normalized interval drops the day total.
  • To average durations it runs `AVG(end - start)` over the interval — average time from signup to first order in one query.
  • Every answer shows the compiled SQL under a trace toggle, so you can confirm the two timestamps and the unit.

Drop into any HTML page

<nlq-data goal="days between each signup and that customer's first order"></nlq-data>

The date subtraction you'd otherwise hand-write — picking the right two timestamps and getting the unit right — is one English goal here, with the SQL shown so you can confirm both.

What this replaces

What nlqdb doesn't try to do here

  • The two timestamps are ones you name — 'signup' and 'first order'. nlqdb computes the difference between the columns you point at; it won't infer which two events bound the duration.
  • A duration is only as correct as the stored timestamps — nlqdb subtracts the values as stored; it doesn't reconcile columns saved in different time zones for you.
  • The public `<nlq-data>` embed is read-scoped — it returns the computed durations, it doesn't write. Persisting a duration column goes through the SDK or `POST /v1/run`.

Questions buyers ask

How do I calculate the time between two dates in SQL?
Ask in plain English — 'days between each signup and that customer's first order.' In Postgres, subtracting one timestamp from another gives an `interval`; for a whole-day count nlqdb compiles `EXTRACT(EPOCH FROM (end - start)) / 86400`. It runs the query in Postgres and shows the SQL, so you can confirm which two timestamps it used and the unit.
Why does EXTRACT(DAY FROM age(...)) give the wrong number of days?
Because `age(end, start)` returns a month-normalized interval: a 40-day gap comes back as '1 mon 9 days', and `EXTRACT(DAY FROM age(…))` returns only that day component, 9, not 40. Plain subtraction (`end - start`) stays in days, but the count that's robust across any gap is `EXTRACT(EPOCH FROM (end - start)) / 86400`. nlqdb compiles the epoch form and shows it.
How do I get the average time between two events in SQL?
Ask for the average directly — 'average time from signup to first order.' In Postgres you can average intervals: `AVG(first_order_at - signed_up_at)` returns an average interval, and dividing the epoch by 86400 gives it in days. nlqdb picks the form from your English and shows the SQL, so the averaged unit is the one you meant.
Can I compute durations on a Postgres database I already run?
Yes — connect it with the signed-in BYO connect verb (`nlq db connect`; see /solve/query-existing-postgres-in-natural-language) and ask for the time between your two columns in place, no ETL into a separate store. The honest limits: BYO connect is signed-in only (not the public embed), and the answer is read-only — nlqdb returns the durations, it doesn't write them back to a column.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Related guides

Try nlqdb in 30 seconds

No sign-in. The anonymous database lasts 72 hours; adopt it with one click if you keep it.

Start with a goal →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.