Обсуждение: parts of date_part()

Hi there,

Well, I've done some digging in the FAQ, the user documentation, and the
man pages, so I hope nobody screams 'RTFM' on this question, but I suppose
you're welcome to if it was somewhere obvious and I missed it.

I'm looking for a complete list of the different "date parts" that I can
use to extract information about a given datetime value in a table.  For
instance, I know that I can do:

select date_part('day', field_name) from table ;

and that will give me the day of the month of that date.  I've also
played with some other things, like 'month' and 'year'.  However, I was
wondering if there was a way to obtain the name of the day (Tuesday,
Wednesday, etc.) using this function, among other things.  I tried

select date_part('weekday', field_name) from table ;

but that didn't work.  Really, what I need is a list of types that
arguments that I can use for the first paramater of this function.  If
this isn't documented now, and someone has answers, I'd be happy to write
something up for the FAQ or the user's manual.

TIA....

eric

_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net

   played with some other things, like 'month' and 'year'.  However, I was
   wondering if there was a way to obtain the name of the day (Tuesday,
   Wednesday, etc.) using this function, among other things.  I tried

Try

    select date_part ('dow', field_name) from table ;

This gives numbers 0-6 => Sunday-Saturday (I think).  I suppose that
could be joined with another table with names.

Is there a more direct way to get the names?

Cheers,
Brook

At 2:33 +0300 on 25/8/98, Eric McKeown wrote:


>
> Well, I've done some digging in the FAQ, the user documentation, and the
> man pages, so I hope nobody screams 'RTFM' on this question, but I suppose
> you're welcome to if it was somewhere obvious and I missed it.
>
> I'm looking for a complete list of the different "date parts" that I can
> use to extract information about a given datetime value in a table.

Actually, it is an RTFM...

From the manpage of pgbuiltin (my postgres version is 6.2.1):

     For the date_part() and  date_trunc()  functions,  arguments
     can   be  `year',  `month',  `day',  `hour',  `minute',  and
     `second',  as  well  as  the  more  specialized   quantities
     `decade',   `century',   `millenium',   `millisecond',   and
     `microsecond'.  date_part() allows `dow' to  return  day  of
     week  and  `epoch' to return seconds since 1970 for datetime
     and 'epoch' to return total elapsed seconds for timespan.

So...

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

According to Herouth Maoz:
>
> Actually, it is an RTFM...
>
> >From the manpage of pgbuiltin (my postgres version is 6.2.1):
>
>      For the date_part() and  date_trunc()  functions,  arguments
>      can   be  `year',  `month',  `day',  `hour',  `minute',  and
>      `second',  as  well  as  the  more  specialized   quantities
>      `decade',   `century',   `millenium',   `millisecond',   and
>      `microsecond'.  date_part() allows `dow' to  return  day  of
>      week  and  `epoch' to return seconds since 1970 for datetime
>      and 'epoch' to return total elapsed seconds for timespan.

Is there any documentation (or is any needed) for the equivalent functions
mapped to their SQL92  "extract (field from value)" syntax?

Since I just ran across:
psql=> select date_part('month','01-01-1998'::date);
date_part
---------
       12
(1 row)
and the extract() does the same in this case, I'm being a little cautious...

 Les Mikesell
   les@mcs.com

At 19:53 +0300 on 26/8/98, Leslie Mikesell wrote:


> Since I just ran across:
> psql=> select date_part('month','01-01-1998'::date);
> date_part
> ---------
>        12
> (1 row)
> and the extract() does the same in this case, I'm being a little cautious...

As a rule, I never use the type DATE, only DATETIME. I've seen too many
problems with timezones with the shorter types, and the functions
supporting them are too few for me to be comfortable with them.

My guess as to the reason of the above - and the Gurus may correct me if
I'm wrong - is that the DATE type does not contain time information. But
then, it is converted internally to DATETIME or milliseconds-since-epoch
for the sake of the function. This conversion involves your timezone, and
since you timezone is probably negative, a few hours before 1998-01-01 is
definitely December.

In my Postgres (Israeli Daylight Time):

testing=> select date_part( 'month', '01-01-1998'::date );
date_part
---------
        1
(1 row)

Then again, it could be a platform problem. Have all the regression tests
passed correctly when you compiled?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma