> On Sat, Aug 05, 2006 at 02:58:01PM +0200, Eric Bollengier wrote:
> > FROM_UNIXTIME(
> > UNIX_TIMESTAMP(Media.LastWritten)
> > + (Media.VolRetention)
> > ) AS expire
>
> Or, if you're using Postgres rather than MySQL...
>
> lastwritten + volretention * interval '1 second' as expire
>
> Do the newer releases of MySQL accept this?
I'm using mysql 4.1 and postgresql 8.1. My code works on mysql/postgresql
without any "if (postgresql) { } else if (mysql) { }", so it's quite clean.
The result look like :
SELECT Media.VolumeName AS volumename,
Media.VolStatus AS volstatus,
Media.LastWritten AS lastwritten,
Media.MediaType AS mediatype,
Media.VolMounts AS volmounts,
Pool.Name AS name,
(
(Media.LastWritten)
+ interval '1 second' * (Media.VolRetention)
) AS expire
FROM Pool, Media INNER JOIN Pool USING (Pool.PoolId = Media.PoolId)
WHERE Media.InChanger <> 1
AND Media.VolStatus IN ('Purged', 'Full', 'Append')
AND Media.Recycle = 1
AND ( (Media.LastWritten)
+ interval '1 second' * (Media.VolRetention)
) < NOW()
AND Media.MediaType IN ('DVD','File')
AND Pool.Name IN ('DVD','Default','Total')
ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
LIMIT 10;
> It would be convenient if there were a single way to code these types
> of "time plus an interval" calculations that would work with both databases.
If you know how do it, i'm interested. But, type, time and string
manipulations are out from SQL standard, and mysql is very strange.
Bye
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
