Home » Other » Suggestions & Feedback » Member's profile: number of average messages per day
| 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
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?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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: 68718
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: 8960
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.
|
|
|
|
| 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: 68718
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:
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
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: 8960
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/
|
|
|
|
Goto Forum:
Current Time: Wed Dec 04 01:52:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
