Hello Kern
,
I noticed that LastWritten value is stored in UTC. Therefore the results of
both queries are in UTC. These values can be converted to
the
database session
local times. Unfortunately, the local
time for a user issuing this query through bconsole can be different from
the database session local time (if the bconsole host is located in a
different time zone than the bacula database
server).
For a PostgreSQL bacula database:
# 21
:List Media for a selected Pool
*Enter Pool name:
SELECT MediaId,VolumeName,Media.VolRetention,Media.LastWritten
at time zone current_setting('TIMEZONE'),to_timestamp(extract('epoch' from
LastWritten)+Media.VolRetention) at time
zone
current_setting('TIMEZONE') as ExpiresAt
FROM Media,Pool
WHERE Name='%1' AND Media.PoolId=Pool.PoolId;
For a MySQL bacula database:
# 21
:List Media for a selected Pool
*Enter Pool name:
SELECT
MediaId,VolumeName,Media.VolRetention,CONVERT_TZ(Media.LastWritten,'+00:00',@@session.time_zone),CONVERT_TZ(FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten)+Media.VolRetention),'+00:00',@@session.time_zone)
AS ExpiresAt
FROM Media,Pool
WHERE Name='%1' AND Media.PoolId=Pool.PoolId;
I removed the "+1" second from both queries since
the result
makes more sense to me. I run tests in a "+01:00" (CET)
time zone offset.
Best regards,
Ana
On Thu, Feb 18, 2016 at 8:44 AM, Kern Sibbald <k...@sibbald.com> wrote:
> Hello Ana,
>
> It may be possible that your code would work. However, please be aware
> that it was *very* complicated to get the current code to produce the right
> results. It is very easy to get an answer that is wrong by 1 hour or
> something like that.
>
> Thus anything that one would submit would need to be *really* well tested.
>
> Best regards,
> Kern
>
>
> On 02/18/2016 10:45 AM, Ana Emília M. Arruda wrote:
>
>> Hello Kern,
>>
>> Thank you for your clear explanation. I was thinking about an
>> "ExpiresAt" field instead of an "ExpiresIn". The meaning of an ExpiresIn
>> field is perfectly clear for me expressed in seconds. Also, I agree that
>> the results from bconsole commands should be less human-readable since
>> we have GUI interfaces for this purpose. IMHO these values should be in
>> a kind of raw format that can make their use easier with/in other
>> languages/frameworks.
>>
>> Maybe the bellow PostgreSQL/MySQL queries (included in query.sql) could
>> help with an "ExpiresAt" field:
>>
>> 21: List Media for a selected Pool
>> Choose a query (1-21): 21
>> Enter Pool name: File
>>
>> +---------+------------+--------------+---------------------+---------------------+
>> | mediaid | volumename | volretention | lastwritten |
>> expiresat |
>>
>> +---------+------------+--------------+---------------------+---------------------+
>> | 1 | Vol-0001 | 31,536,000 | 2016-01-21 23:10:03 | 2017-01-20
>> 23:10:04 |
>>
>> +---------+------------+--------------+---------------------+---------------------+
>>
>> For a PostgreSQL bacula database:
>>
>> # 21
>> :List Media for a selected Pool
>> *Enter Pool name:
>> SELECT
>>
>> MediaId,VolumeName,Media.VolRetention,Media.LastWritten,to_timestamp(extract('epoch'
>> from LastWritten)+Media.VolRetention+1) at time zone 'UTC' as ExpiresAt
>> FROM Media,Pool
>> WHERE Name='%1' AND Media.PoolId=Pool.PoolId;
>>
>>
>> For a MySQL bacula database:
>>
>> # 21
>> :List Media for a selected Pool
>> *Enter Pool name:
>> SELECT
>>
>> MediaId,VolumeName,Media.VolRetention,Media.LastWritten,FROM_UNIXTIME(UNIX_TIMESTAMP(LastWritten)+Media.VolRetention+1)
>> AS ExpiresAt
>> FROM Media,Pool
>> WHERE Name='%1' AND Media.PoolId=Pool.PoolId;
>>
>> I'm not sure about adding one second to this value...
>>
>> Am I misunderstanding something here?
>>
>> Thank you.
>>
>> Best regards,
>> Ana
>>
>>
>>
>> On Thu, Feb 18, 2016 at 12:58 AM, Kern Sibbald <k...@sibbald.com
>> <mailto:k...@sibbald.com>> wrote:
>>
>> Hello Heitor,
>>
>> One of the basic design conditions for Bacula is that as much as
>> possible that goes into it officially should be understandable and
>> maintainable by an average C/C++ programmer. Once things start
>> getting complicated, I slow down ...
>>
>> Best regards,
>> Kern
>>
>> On 02/18/2016 08:25 AM, Heitor Faria wrote:
>>
>> Kern,
>>
>> Sorry for the dumb question but could not bconsole make the values
>> conversion in similar way to this shell script bytes conversion
>> snippet
>> before printing?
>>
>> xargs -i echo 'scale=2; {}/1073741824' | bc
>>
>> May it would be a more database independent approach.
>>
>> Regards,
>>
>> ===========================================================================
>> Heitor Medrado de Faria - LPIC-III | ITIL-F | Bacula Systems
>> Certified
>> Administrator II
>> Do you need Bacula training?
>> <http://bacula.us/video-classes>http://bacula.us/video-classes/
>> +55 61 8268-4220 <tel:%2B55%2061%208268-4220>
>> Site: <http://bacula.us>http://bacula.us FB: heitor.faria
>>
>> ===========================================================================
>>
>> Enviado por TypeApp <http://www.typeapp.com/r>
>>
>> Em 17 de fev de 2016, em 20:05, Kern Sibbald <k...@sibbald.com
>> <mailto:k...@sibbald.com>
>> <mailto:k...@sibbald.com <mailto:k...@sibbald.com>>> escreveu:
>>
>>
>>
>> Hello Ana (and Heitor),
>>
>> Please note that at the current time, the list command is
>> generic, and
>> thus it only knows how to print character strings that have
>> been
>> returned by the SQL engine. Though there may be some way
>> to tell SQL
>> that the result we get for expiresin is a "duration" (note:
>> very
>> different from a date such as LastWritten), I do not know
>> how to do it.
>>
>> Thus for the moment, we are limited to displaying SQL
>> generated
>> character strings in the form that SQL gives them to us.
>>
>> The main point of the new expiresin field is that it is not
>> zero, the
>> retention period has not expired. If it is a big positive
>> number
>> (number of seconds remaining before the volume expires),
>> then the Volume
>> will not be recycled.
>>
>> Many people forget that the retention period *begins* from
>> the
>> LastWritten time, which means that as long as you are
>> writing on the
>> Volume, nothing will be expired. Maybe devoting a bit of
>> thought to
>> that particular point, and what would happen if we changed
>> it, would
>> make retention periods easier to understand.
>>
>> Best regards,
>> Kern
>>
>> On 02/16/2016 07:19 PM, Ana Emília M. Arruda wrote:
>>
>> Hello Kern and Heitor,
>>
>> I can see the ExpiresIn field in Bacula 7.4.0 version.
>> Maybe the ExpiresIn value could be more useful if
>> displayed in
>> the same
>> format as the LastWritten field.
>>
>> Best regards,
>> Ana
>>
>> On Tue, Feb 16, 2016 at 2:46 AM, Heitor Faria
>> <hei...@bacula.com.br <mailto:hei...@bacula.com.br>
>> <mailto:hei...@bacula.com.br
>>
>> <mailto:hei...@bacula.com.br>>> wrote:
>>
>> Recently I got tired of doing the mental
>> gymnastics
>> to see when Volumes
>> will expire and with Eric's SQL help, we
>> modified
>> the list (and llist)
>> media output to eliminate one or two of the
>> columns
>> in the case of list
>> media, but to add an "expiresin" field,
>> which makes
>> it much easier to
>> see when a volume will expire.
>>
>> This code has been in the public git
>> repository in
>> Branch-7.4 along with
>> a number of bug fixes since the 7.4.0
>> release. If
>> you are interested in
>> simplifying the Volume expiration mind
>> gymnastics
>> you might checkout and
>> try the new code.
>>
>> By the way, I thought that the above
>> feature was
>> added after the 7.4.0
>> release, but according to what I see in the
>> repo,
>> much to my surprise it
>> should also be in the released 7.4.0 version.
>>
>> I would be interested in any feedback.
>>
>>
>> Hello, Kern: this feature is great and I'm
>> thankful for
>> it, but is there a way
>> to make information human readable?
>>
>>
>> What do you mean?
>>
>> Please show me what you currently see and what you
>> prefer to
>> see.
>>
>>
>> Hello, Kern: sorry for being laconic.
>> When I mean "human readable" is in the sense of ls, df
>> and other
>> Linux commands:
>>
>> "-h, --human-readable
>> print sizes in human readable format (e.g., 1K 234M 2G)"
>>
>> *What I see today:*
>>
>> *list media pool=File
>>
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | MediaId | VolumeName | VolStatus | Enabled | VolBytes |
>> VolFiles | VolRetention | Recycle | Slot | InChanger |
>> MediaType |
>> LastWritten | ExpiresIn |
>>
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | 1 | Vol-0001 | Error | 1 | 1,286,119,412 |
>> 0 | 31,536,000 | 1 | 0 | 0 | File1 |
>> 2015-10-21 23:57:35 | 21,428,702 |
>>
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>>
>> *What I think would make the user life easier:*
>>
>> *list media pool=File human
>>
>>
>> +---------+------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | MediaId | VolumeName | VolStatus | Enabled | VolBytes
>> | VolFiles |
>> VolRetention | Recycle | Slot | InChanger | MediaType |
>> LastWritten
>> | ExpiresIn |
>>
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | 1 | Vol-0001 | Error | 1 | 1,29 GB | 0
>> | 31,536,000 | 1 | 0 | 0 | File1 | 2015-10-21
>> 23:57:35 | 248 days |
>>
>>
>> +---------+------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>>
>> Best regards,
>> Kern
>>
>>
>> Regards,
>> --
>>
>>
>> ------------------------------------------------------------------------
>>
>> Heitor Medrado de Faria - LPIC-III | ITIL-F | Bacula
>> Systems
>> Certified Administrator II
>> Do you need Bacula training?
>> http://bacula.us/video-classes/
>> +55 61 8268-4220 <tel:%2B55%2061%208268-4220>
>> <tel:%2B55%2061%208268-4220>
>> Site: http://bacula.us FB: heitor.faria
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> Site24x7 APM Insight: Get Deep Visibility into
>> Application
>> Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just
>> $35/Month
>> Monitor end-to-end web transactions and take corrective
>> actions now
>> Troubleshoot faster and improve end-user experience.
>> Signup Now!
>> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>>
>>
>> ------------------------------------------------------------------------
>>
>> Bacula-users mailing list
>> Bacula-users@lists.sourceforge.net
>> <mailto:Bacula-users@lists.sourceforge.net>
>> <mailto:Bacula-users@lists.sourceforge.net
>> <mailto:Bacula-users@lists.sourceforge.net>>
>> https://lists.sourceforge.net/lists/listinfo/bacula-users
>>
>>
>>
>>
>>
------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
