Re: date with month and year

On 05/21/2015 11:02 AM, Daniel Torres wrote:
> Sorry, forgot to told you what I'm trying, I have climate data and want
> to obtain mean temperature and total precipitation and that sort of
> things per month and year. Think date_trunc is a good solution, but any
> other advice would be very welcome.

As it turns out I am working on something similar with regards to school
days. A quick and dirty query:

SELECT
extract (
YEAR
FROM
school_day ) AS YEAR,
extract (
MONTH
FROM
school_day ) AS MONTH,
count (
school_day )
FROM
school_calendar
GROUP BY
extract (
YEAR
FROM
school_day ),
extract (
MONTH
FROM
school_day )
ORDER BY
extract (
YEAR
FROM
school_day ),
extract (
MONTH
FROM
school_day );

Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/).

Results:

year | month | count
------+-------+-------
2005 | 3 | 7
2005 | 4 | 12
2005 | 5 | 17
2005 | 6 | 14
2005 | 7 | 11
2005 | 8 | 15
2005 | 9 | 16
2005 | 10 | 15
2005 | 11 | 17
2005 | 12 | 10
2006 | 1 | 15
2006 | 2 | 12
2006 | 3 | 18
2006 | 4 | 12
2006 | 5 | 18
2006 | 6 | 13
2006 | 7 | 11
2006 | 8 | 15
2006 | 9 | 15
2006 | 10 | 18
2006 | 11 | 13
2006 | 12 | 10

>
> (I need to read more about time zones, I'm new at using postgresql)
>
> Thank you,
> Daniel
>
> 2015-05-21 12:45 GMT-05:00 Paul Jungwirth <pj(at)illuminatedcomputing(dot)com
> <mailto:pj(at)illuminatedcomputing(dot)com>>:
>
> You really shouldn't use WITHOUT TIME ZONE.
>
>
> I'd like to know more about this. Can you say why? Are there any
> articles you'd recommend? I'm fond of normalizing all times to UTC
> and only presenting them in a time zone when I know the current
> "perspective". I've written about that approach in a Rails context here:
>
> http://illuminatedcomputing.com/posts/2014/04/timezones/
>
> I find that this helps me to ignore time zones in most parts of my
> application and cut down on my timezone-related bugs.
>
> Thanks!
>
> Paul
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com