CODEWITHSUNDEEP
sqlpostgresql
DBE7
DBE7

Reputation: 816

Difficulties retrieving data with SQL now() function

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.

Upvotes: 0

Views: 40

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51446

Please explain what is your problem - here's example of working query you say does not work:

enter image description here EDIT:

select created_at,extract(day from created_at),DATE_PART('day', now()),extract(day from now()),now() from be where DATE_PART('day', be.created_at) = DATE_PART('day', now())

enter image description here

EDIT2

just in case you are trying to compare today agaist date in row, then use date_trunc, like:

select date_trunc('day',created_at), date_trunc('day',now()) from be ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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

Upvotes: 1

Related Questions