Thread

1. parts of date_part()

   Eric J McKeown <ericm@palaver.net> — 1998-08-24T23:33:53Z

   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
   
   
   

2. Re: [SQL] parts of date_part()

   Brook Milligan <brook@trillium.nmsu.edu> — 1998-08-25T17:20:18Z

      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
   
   

3. Re: [SQL] parts of date_part()

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-08-26T08:57:16Z

   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
   
   
   
   

4. Re: [SQL] parts of date_part()

   Leslie Mikesell <les@mcs.net> — 1998-08-26T16:53:39Z

   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
   
   
   

5. Re: [SQL] parts of date_part()

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-08-27T08:13:10Z

   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