sql functions

[dwhalen]

Hey,

I am trying to take two dates, end date and start date, and find the difference between the two. I am then going to add all the differences up to find an average length of time based on a certain criteria. How do I go about finding the difference between the two dates, is there a function? I seem to remember using a interval function or something like that but I can't find it anywhere in the book I have.

Also I was wondering about string manipulation functions, I work with perl, so I was thinking along the lines of a split function; I want to split a field on the | symbol (don't ask why I just have to), does sql have this sort of power or am I going to have to find another way around.

Maybe someone could point me to a good website that has lots of examples and stuff or failing that maybe a good book.

Thanks for the help

 

[rycamor]

"interval" is a datatype, not a function. There are several time manipulation functions that can be performed with timestamps and intervals. The interval datatype is one of my favorite features of PostgreSQL, because it greatly simplifies many calculations involving time, and it uses a nice human-readable formatting ('3 years 9 months 27 days 1 hour 1 minute 45 seconds').

For example, if you want get the difference between to timestamp dates, you could do something like:

SELECT (end_date - start_date)::interval AS my_time_span FROM my_table

(the "::" is the shorthand way of typcasting this to an interval datatype)

See the following links:

http://www.postgresql.org/idocs/index.php?functions-datetime.html

http://www.postgresql.org/idocs/index.php?datatype-datetime.html

for information on the time datatypes.

http://www.ca.postgresql.org/docs/aw_pgsql_book/node90.html#SECTION001723000000000000000

-------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[rycamor]

Also, PostgreSQL, as most SQL implementations, has all kinds of string-manipulation functions which can do all you want and more.

http://www.postgresql.org/idocs/index.php?functions-string.html

Also, PostgreSQL implements POSIX regular expressions into SQL queries. It's not Perl, but it still gives you a LOT more power over your strings than standard SQL.

http://www.postgresql.org/idocs/index.php?functions-matching.html

For a good book on PostgreSQL's abilities, see "PostgreSQL Developer's Handbook" by Geschwinde and Schoenig. -------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[dwhalen]

Thank you so much, that was a big help! I love these forums!

Later