[Cialug] SQL wizardry possible?

[Matthew Nuzum]

create temp table matt_tmp (pref_uid varchar(6), pref_value text);
insert into matt_tmp values
('dar012','a:2:{s:4:"time";i:1193777972;s:4:"host";s:11:"10.11.3.162";}');

select substring(pref_value from position(';i:' in pref_value) +3 for
10) from matt_tmp;
 substring
------------
 1193777972

You can then either cast this to a date, or maybe better, cast it to
an integer, subtract it from the current epoch and then filter based
on if the interval is longer than you want.

Since this is all sql its a snap to make this into a user defined function.

On 10/30/07, Daniel A. Ramaley <daniel.ramaley at drake.edu> 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
>


-- 
Matthew Nuzum
newz2000 on freenode