Thread: 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