[Cialug] SQL wizardry possible?

Daniel A. Ramaley daniel.ramaley at DRAKE.EDU
Tue Oct 30 16:22:03 CDT 2007

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:"";}

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.

Dan Ramaley                            Dial Center 118, Drake University
Network Programmer/Analyst             2407 Carpenter Ave
+1 515 271-4540                        Des Moines IA 50311 USA

More information about the Cialug mailing list