Working with timestamp values in select statements to filter output by date.

Question & Answer

Question

How can timestamp values available in the Tivoli Storage Manager server tables be used in select statements to filter data on dates and time?

Cause

Using timestamp values in a Tivoli Storage Manager select statement can be a challenge when trying to get data for a specific date or time period.

Answer

Proper casting and or standard SQL date/time functions can be used to help filter on date and time when there is a timestamp field in a table being processed.

To filter on a timestamp and display those items with a date/time of "today" the following syntax can be used:

where date(current_timestamp)-date(<timestamp value from table>)=0 For example to obtain a list of volumes from storage pool TAPEPOOL with a last write date of today, the following select statement could be used:

select VOLUME_NAME, LAST_WRITE_DATE from VOLUMES where STGPOOL_NAME=TAPEPOOL' and date(current_timestamp)-date(LAST_WRITE_DATE)=0 To get the list of volumes from the same pool that have a write date within the last week the same select statement could be used by changing the '=0' to a '<=7'

When filtering on a timestamp to display only items for a specific day, the following syntax can be used:

where date(<timestamp from table>)=date('<desired search date>') For example to obtain a list of volumes from storage pool TAPEPOOL that were written on April 22nd 2014, the following select statement could be used:

select VOLUME_NAME, LAST_WRITE_DATE from VOLUMES where STGPOOL_NAME='TAPEPOOL' and date(LAST_WRITE_DATE)=date('2014-04-22')
It is also possible to filter on a timestamp to obtain a listing that covers a specific date range by using the following syntax:

where date(<timestamp from table>)>=date('<desired begin date>') and date(<timestamp from table>)<=date('<desired end date>') For example to obtain a list of volumes from storage pool TAPEPOOL that were all written in the month of January 2014 the following select statement could be used:

select VOLUME_NAME, LAST_WRITE_DATE from VOLUMES where STGPOOL_NAME='TAPEPOOL' and date(LAST_WRITE_DATE)>=date('2014-01-01') and date(LAST_WRITE_DATE)<= date('2014-01-31')

[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Server","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Supported Versions","Edition":"Edition Independent","Line of Business":{"code":"LOB26","label":"Storage"}}]

Product Synonym

TSM

Was this topic helpful?

Document Information

Modified date:
17 June 2018

UID

swg21670240

Tips