Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Filtering on Time

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Filtering on Time
 Login/Join
 
darrell_of_ulm
Member
posted
Primary task: filtering on the Time an entry was made.

We have a date field that shows only the date by default, so I need to display the time, then extract the time (hour only should suffice) and filter on that in order to see entries that were made after a certain hour of the day.

I'm fairly new to WebFOCUS so a simpler path would make my day...

Thanks.

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 6 | Registered: October 24, 2016Report This Post
Wep5622
Virtuoso
posted Hide Post
What is the data-format of the field?

Assuming HYYMD or similar, that does only display the date, but the format includes time too.

Hence, you can filter it somehting like this:
WHERE DATE_FIELD GT DT('2017-03-29 14:00:00');


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
BabakNYC
Virtuoso
posted Hide Post
What's the exact format of the date field? Do you have access to the Metadata?

Search documentation for Date Time functions. You'll find a whole bunch of functions you can use for this depending on the format of the field.

http://infocenter.informationb...om/wf81rel/index.jsp


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Francis Mariani
Expert
posted Hide Post
The problem might be that the filter is for a particular hour of any day, or a series of days.

You can DEFINE or COMPUTE a new column and use the HPART function to extract the hour, then use this new column in your filter.

HPART(datetime, 'component', output)


Look into Using Functions > Date-Time Functions > HPART: Retrieving a Date-Time Component as a Numeric Value


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
darrell_of_ulm
Member
posted Hide Post
The format is HYYMD. I've looked at various Functions but none are working for me. I'm not sure if my syntax is wrong of if I'm choose the wrong function for the format.

And I'd like to first display the time so I can be sure that any filtering on the hour is working correctly. (I like to double-check my code that way.)


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: October 24, 2016Report This Post
Francis Mariani
Expert
posted Hide Post
You should be able to DEFINE or COMPUTE a new column with the HYYMDS format:

DATE_COL_TIME/HYYMDS = DATE_COL;


Then you should be able to use the HPART function:

DATE_COL_TIME_HH/I2 = HPART(DATE_COL_TIME, 'HOUR', 'I2');


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
darrell_of_ulm
Member
posted Hide Post
Francis,

The result of this:

COMPUTE R_TIMEF/HYYMDS = [date field];
COMPUTE R_TIME/I2 = HPART(R_TIMEF, 'HOUR', 'I2');

is this:

RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: October 24, 2016Report This Post
Francis Mariani
Expert
posted Hide Post
That error must be for the R_TIMEF - did you get a line number?

Please post the definition of the date field as seen in the master.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
darrell_of_ulm
Member
posted Hide Post
That is the line with the problem.

The field I'm working on has the format YYMD in the original.


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: October 24, 2016Report This Post
Francis Mariani
Expert
posted Hide Post
The field usually has ACTUAL and USAGE formats. If the ACTUAL is YYMD you can't squeeze time out of it.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
darrell_of_ulm
Member
posted Hide Post
But I can still get the Time to display along with the Date, correct?

If so, how would I do that given the current format?


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: October 24, 2016Report This Post
Francis Mariani
Expert
posted Hide Post
quote:
Please post the definition of the date field as seen in the master.


E.g.

    FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $

    FIELDNAME=START_DATE_TIME, ALIAS=START_DATE_TIME, USAGE=HYYMDS, ACTUAL=HYYMDS, $


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Even better, show us how the field is defined in the RDBMS table.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
darrell_of_ulm
Member
posted Hide Post
Format on master:

Name: R_Date
Qualified Name: GLTRANS.GLTRANS.R_DATE
Alias: R_DATE
Title: R_DATE
Format: YYMD
Type: Real

I'm still quite new to WebFOCUS so the RDMBS table is foreign to me...sorry.


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: October 24, 2016Report This Post
Francis Mariani
Expert
posted Hide Post
RDBMS = relational database management system. Oracle, Microsoft SQL Server, MySQL.

I'm not sure what "Type: Real" means.

How do you know that R_Date contains Date and Time?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Filtering on Time

Copyright © 1996-2020 Information Builders