[Cialug] SQL wizardry possible?

[Jeffrey C. Ollie]

On Tue, 2007-10-30 at 16:22 -0500, 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?
> 
> If it is not possible with pure SQL, then i already know how to do 
> something with Perl, i'm just trying to find a more elegant way that 
> doesn't mix as many languages.

Completely untested, but:

select pref_uid,pref_value from horde_prefs where pref_name = 'last_login' and age(to_timestamp(to_number(substring(pref_value from 'i:([0-9]+);'))) < interval '6 months';

http://www.postgresql.org/docs/8.2/interactive/functions-string.html
http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

Jeff

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://cialug.org/pipermail/cialug/attachments/20071030/8fc6ffd3/attachment.pgp