[Cialug] SQL wizardry possible?

[Dave Weis]

Daniel A. Ramaley wrote:
> There is a rather complex SQL (specifically, PostgreSQL) query that i 
> want to do. I don't know if it is possible with pure SQL.
> If i run this query:
> select pref_uid,pref_value from horde_prefs where pref_name = 'last_login';
> I get back many rows of output. Here's a sample of the pref_uid and 
> pref_value data:
>   pref_uid: dar012
> pref_value: a:2:{s:4:"time";i:1193777972;s:4:"host";s:11:"10.11.3.162";}
> What i want to do it pull out the time stamp that is embedded in 
> pref_value. In this example it is "1193777972". And then based on that 
> time stamp, i want to only return records if the time stamp is less 
> than some other time stamp (now minus 6 months, or roughly 1178226993 at 
> the time of this writing). Is it possible with SQL to pull out a 
> substring and then do a comparison on it?

Check out http://www.postgresql.org/docs/8.0/static/functions-string.html
split_part. It's most likely not ANSI SQL but I know Oracle has 
something similar also so you can fake your way on other RDMBS's if you 
ever have to move.
After you get out the number you want you can use their casting and date 
math to get the numbers in the format you want.

dave