Postgres Pro
Downloads
Home   >   mailing lists

Thread: Extract date portion of a datetime field

Extract date portion of a datetime field

From
JORGE MALDONADO
Date:
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

Re: Extract date portion of a datetime field

From
Tom Lane
Date:
JORGE MALDONADO <jorgemal1960@gmail.com> writes:
> 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

Re: Extract date portion of a datetime field

From
Scott Ford
Date:
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

On Wed, Apr 18, 2018 at 10:01 AM JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
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
f:  416.360.8385

w. bullfrogpower.com


    

Re: Extract date portion of a datetime field

From
"David G. Johnston"
Date:
On Wed, Apr 18, 2018 at 7:01 AM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
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.​

RE: Extract date portion of a datetime field

From
Stephen Froehlich
Date:

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 <jorgemal1960@gmail.com>
Sent: Wednesday, April 18, 2018 8:01 AM
To: pgsql-novice <pgsql-novice@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