OraFAQ Forum: Suggestions & Feedback » Member's profile: number of average messages per day

Home » Other » Suggestions & Feedback » Member's profile: number of average messages per day

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Member's profile: number of average messages per day [message #684091]

Mon, 05 April 2021 14:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I've stumbled upon zozogirl's account. Although she last visited forum almost 10 years ago, I remembered she got nice posts here.

Her profile says that average number of messages per day = 0.01, so I thought that perhaps it would be more fair if that number was calculated for the active period, not since the registration date up to "today".

For her, that would be:

- registered: November 08, 2005
- last visited: February 17, 2012

Code: [Select all] [Show/ hide]

SQL> select
  2    round(77 / (trunc(sysdate)    - date '2005-11-08'), 2) old_avg_msg_per_day,
  3    round(77 / (date '2012-02-17' - date '2005-11-08'), 2) new_avg_msg_per_day
  4  from dual;

OLD_AVG_MSG_PER_DAY NEW_AVG_MSG_PER_DAY
------------------- -------------------
               0,01                0,03

SQL>

Not that she (or anyone else) cares much about it; I just thought that it would be better information. Now, would we take last visited date or last message date, I can't tell; I prefer last visited date.

What do you think?

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684092 is a reply to message #684091]

Mon, 05 April 2021 15:26

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think this is a good idea.
For my part, I will update the Forum statistics in this way (not for the current ones which have already been computed but not posted but for the next quarter) unless most of us disagree.

[Updated on: Mon, 05 April 2021 15:28]

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684096 is a reply to message #684092]

Tue, 06 April 2021 12:30

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

However, there may be a counter-argument: imagine the case of someone who registers, posts a question, has an answer and thanks within one hour and never visits again, he/she would get a record average of 2/(1/24) = 48 messages per day!
It would be unfair for the regulars who post here for years and can't obviously hold such a rate.

A solution may be to have a message like "48 msg/day over 0.04 day".

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684098 is a reply to message #684096]

Tue, 06 April 2021 14:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Right. How about this? I used date literals; they represent TRUNC-ated datetime values of registered and last visited dates.

Your imaginary user would have this average:

Code: [Select all] [Show/ hide]

SQL> select
  2   round(2 / (greatest(date '2021-04-06' - date '2021-04-06', 1)), 2) avg_msg_per_day
  3                       -----------------   -----------------
  4                     --last visited        date registered
  5  from dual;

AVG_MSG_PER_DAY
---------------
           2,00

SQL>

You (Michel) would have

Code: [Select all] [Show/ hide]

SQL> select
  2   round(67799 / (greatest(date '2021-04-06' - date '2007-03-18', 1)), 2) avg_msg_per_day
  3                           -----------------   -----------------
  4                         --last visited        date registered
  5  from dual;

AVG_MSG_PER_DAY
---------------
          13,21

SQL>

which is exactly what you really have at the moment.

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684099 is a reply to message #684098]

Wed, 07 April 2021 00:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

So, counting each day as a whole:

<nb msg> / (trunc(<last visited>)-trunc(<date registered>)+1)

[Updated on: Wed, 07 April 2021 00:52]

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684100 is a reply to message #684099]

Wed, 07 April 2021 00:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

But TRUNC depends on the time zone (it is not midnight at the same time everywhere in the world), so the result will depend on the user location.

Better would be:

<nb msg> / (trunc(<last visited>-<date registered>)+1)

[Updated on: Wed, 07 April 2021 00:54]

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684101 is a reply to message #684100]

Wed, 07 April 2021 01:21

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I thought that dates represent "database server" (which hosts OraFAQ) datetime value, not local to each of Forum members. Would "hours" make a significant difference? If it is about members who post once and never again, their average will be small whatever you do. But, for e.g. JRowbottom, a few hours here or there? Who cares?

Anyway, as far as I'm concerned, I'm fine with any option you choose.

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684102 is a reply to message #684101]

Wed, 07 April 2021 01:29

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I thought that dates represent "database server" (which hosts OraFAQ) datetime value,

Datetimes are stored in Linux format (number of seconds since 01-01-1971 00:00:00 at time zone 0) but they are displayed in your time zone.

We are in the same tone zone so I can't show you the difference but someone else could tell us: your last message was, for me (and you), on (as displayed) "Wed, 07 April 2021 08:21".

[Updated on: Wed, 07 April 2021 01:29]

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684103 is a reply to message #684102]

Wed, 07 April 2021 01:42

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Right! I see that date as

Wed, 07 April 2021 07:21

because my PC is on British Summer Time, UTC+1. So it would appear to be working like an Oracle TIMESTAMP WITH LOCAL TIMEZONE column: stored in the DB time zone, and adjusted for display to the client's time zone.

I have to say that I have always had trouble with this business, the local time zone datatype particularly. When does the conversion occur? If it is at the final stage of running the SQL (during the column projection) then I don't see how it can work correctly for row selection. But if it is done earlier so it can be used in selection, then how can histograms be valid for the parse? I have a crude work around: don't use it. Probably not a very good solution.

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684104 is a reply to message #684103]

Wed, 07 April 2021 03:10

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A pretty good remark, I never thought about it, so made a test with the only TIMESTAMP WITH LOCAL TIMEZONE in my databases: OE.ORDERS.ORDER_DATE:

Code: [Select all] [Show/ hide]

SQL> select * from  OE.ORDERS where trunc(ORDER_DATE) = date '2008-08-01';
  ORDER_ID ORDER_DATE              ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
---------- ----------------------- -------- ----------- ------------ ----------- ------------ ------------
      2441 01/08/2008 10:22:48.734 direct           106            5      2075.2          160

1 row selected.


SQL> alter session set time_zone='-12:00';

Session altered.

SQL> select * from  OE.ORDERS where trunc(ORDER_DATE) = date '2008-08-01';

no rows selected

So, it works for row selection.
Now, remains the question about the histograms.
Maybe a new topic in the "SQL & PL/SQL", "Server Administration" or "Performance Tuning" forum.

[Updated on: Wed, 07 April 2021 03:13]

Report message to a moderator

Re: Member's profile: number of average messages per day [message #684105 is a reply to message #684104]

Wed, 07 April 2021 03:31

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Thinking it through, I can see that there should be no problems with histograms. A TSLTZ column stores data normalized to the DB timezone (which is why you cannot change the DB time zone if there is a TSLTZ column anywhere, because the data would all be wrong afterwards. Unless the change were to include updating every row, of course). If Oracle gathers stats on the TSLTZ column, then all it needs to do is convert the value in the predicate to the DB time zone and the stats will be valid, no matter what your TZ is. Using an index would also be no problem, as that too will be based on the normalized values. So my guess is that the conversion is happening in PGA, with only normalized data in SGA structures.

However, I still think it is really weird: that changing your TZ can affect the result of a query. It means that a simple query is in fact non-deterministic. Does that comply with the SQL standard?

This article gives a hint of what might be going on with a similar oddity:
https://jonathanlewis.wordpress.com/2019/05/29/timestamp-oddity/

Report message to a moderator

Previous Topic:	Just wanted to say Thank you to the maintainers of this forum
Next Topic:	Search feature

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 11:53:48 CDT 2025