[Cialug] SQL wizardry possible?

[Claus]

This should be possible.  Use the "substr" function to get to the time 
stamp.  Then use some date/timestamp functions to do the comparison and 
date arithmetic.

   Claus

On 10/30/2007 4:22 PM, 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.
> 
> ------------------------------------------------------------------------
> Dan Ramaley                            Dial Center 118, Drake University
> Network Programmer/Analyst             2407 Carpenter Ave
> +1 515 271-4540                        Des Moines IA 50311 USA
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug
>