David Bentzon-Ehlers David Bentzon-Ehlers - 5 months ago 10
SQL Question

Difficulties retrieving data with SQL now() funtion

I am trying to retrieve some data utilising SQL's

now()
function. It works problem free for monthly and weekly queries. However, when I try to pull daily data, the numbers are way off. Sample code I am using:

SELECT
COUNT(distinct be.booking_id)AS "Number of Inquiries"
FROM booking_events be
WHERE be.event = 'inquire'
AND DATE_PART('day', be.created_at) = DATE_PART('day', now())


I tried figuring it out for a couple of days, but without any luck.

Answer

Hmmm, if you want today's data, I might suggest:

SELECT COUNT(distinct be.booking_id)AS "Number of Inquiries"
FROM booking_events be
WHERE be.event = 'inquire' AND
      be.created_at >= current_date and
      be.created_at < current_date + interval '1 day';

I don't think date_part() helps you unless you want all records from the same day of the month -- say, Jan 25, Feb 25, Mar 25, and so on.

You might be thinking of date_trunc() instead:

WHERE be.event = 'inquire' AND
      date_trunc(be.created_at) = current_date