Member Avatar for rakeshiapps

hi all,
can some one help me how to display the fields which enter in current week,current month like that

  • 3 Contributors
  • 2 Replies
  • 120 Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by like_to_learn

Dani AI

Generated

asked how to show rows entered in the current week/month. rightly asked for clarity, and suggested flashback. Flashback can show a table’s past state, but for the common case you want “rows inserted in this period” the straightforward, reliable method is to filter on a creation timestamp column (for example created_date or created_ts). If the table has no such column, none of the simple SELECT filters will reliably tell when each row was inserted.

Use range predicates (half-open intervals) based on TRUNC and SYSDATE so the optimizer can use an index on the date column and you avoid off-by-one issues with time components:

-- current day (midnight .. now)
SELECT * FROM your_table
WHERE created_date >= TRUNC(SYSDATE)
  AND created_date < TRUNC(SYSDATE) + 1;

-- current week (ISO week starting Monday)
SELECT * FROM your_table
WHERE created_date >= TRUNC(SYSDATE, 'IW')
  AND created_date < TRUNC(SYSDATE, 'IW') + 7;

-- current month
SELECT * FROM your_table
WHERE created_date >= TRUNC(SYSDATE, 'MM')
  AND created_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);

Notes and troubleshooting:

  • Avoid TRUNC(created_date) = ... because applying functions to the column prevents index range scans.
  • If created_date is a TIMESTAMP WITH TIME ZONE, use SYSTIMESTAMP or cast appropriately.
  • If there is no insert timestamp: add a DEFAULT SYSDATE or a BEFORE INSERT trigger to populate one, or (with important caveats) inspect ORA_ROWSCN — but ORA_ROWSCN is block-granular unless the table was created with ROWDEPENDENCIES and so is only an approximation.
  • Flashback (as mentioned) is a different tool: it reconstructs past states from undo and requires sufficient undo retention; it’s not a substitute for having an explicit creation timestamp when you need exact insert times.
  • For large tables consider an index on the timestamp column or range partitioning by month to keep these queries fast.

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

please post your question clearly.

Member Avatar for like_to_learn

I think below queries may help you.........................

To see the one day before data, use the following Query: select * from <table_name) as of timestamp (systimestamp - interval '1' day) To see the one Hour before data, use the following Query: select * from <table_name) as of timestamp (systimestamp - interval '1' hour)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.