×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

sql functions

sql functions

sql functions

(OP)
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

RE: sql functions

"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)

RE: sql functions

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)

RE: sql functions

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

Later

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close