Extract date portion of a datetime field

[JORGE MALDONADO]

Hello,

I have a table with a datetime field which saves (of course) date and time
information. I need to design a query to count the number of records of
each date without taking into account the time portion. I have seen that I
can use functions like EXTRACT but I do not find how to extract the whole
date only.

I will very much appreciate your feedback.

Respectfully,
Jorge Maldonado

[Tom Lane]

Post by JORGE MALDONADO
I have a table with a datetime field which saves (of course) date and time
information. I need to design a query to count the number of records of
each date without taking into account the time portion. I have seen that I
can use functions like EXTRACT but I do not find how to extract the whole
date only.

Cast to date?

regards, tom lane

[Scott Ford]

I've used something like this in the past:

SELECT COUNT(p.index_value)
, p.create_datetime::date
FROM people p
GROUP BY p.create_datetime::date

Simple, but it works.

Scott

Post by JORGE MALDONADO
Hello,
I have a table with a datetime field which saves (of course) date and time
information. I need to design a query to count the number of records of
each date without taking into account the time portion. I have seen that I
can use functions like EXTRACT but I do not find how to extract the whole
date only.
I will very much appreciate your feedback.
Respectfully,
Jorge Maldonado
--

Scott Ford
Director of Development


*Bullfrog Power Inc.*366 Adelaide Street West, Suite 701
Toronto, ON M5V 1R9

t: 416.300.8443 <(416)%20360-3464>
f: 416.360.8385 <(416)%20360-8385>

w. bullfrogpower.com <http://www.bullfrogpower.com/>

<https://www.facebook.com/BullfrogPower>
<https://twitter.com/bullfrogpower>
<https://www.linkedin.com/company/bullfrog-power>
<https://www.instagram.com/bullfrogpower/>

[David G. Johnston]

Post by JORGE MALDONADO
Hello,
I have a table with a datetime field which saves (of course) date and time
information. I need to design a query to count the number of records of
each date without taking into account the time portion. I have seen that I
can use functions like EXTRACT but I do not find how to extract the whole
date only.

​select ('2018-04-18T12:24:36'::timestamptz)::date
select to_char(('2018-04-18T12:24:36'::timestamptz), 'YYYY-MM-DD')

David J.​

[Stephen Froehlich]

If its already saved as a timestamp with timezone (or timestamp without timezone) in postgres, then date_trunc(‘day’, [field_name]) will do the trick.

--Stephen

From: JORGE MALDONADO <***@gmail.com>
Sent: Wednesday, April 18, 2018 8:01 AM
To: pgsql-novice <pgsql-***@postgresql.org>
Subject: Extract date portion of a datetime field

Hello,

I have a table with a datetime field which saves (of course) date and time information. I need to design a query to count the number of records of each date without taking into account the time portion. I have seen that I can use functions like EXTRACT but I do not find how to extract the whole date only.

I will very much appreciate your feedback.

Respectfully,
Jorge Maldonado