Unexpected results from SQL query with BETWEEN timestamps
Check the data type of the columns and your time zone. You may be confusing timestamp with time zone and timestamp.
Looks like you have timestamp in your table, but query with timestamptz. This way, it all depends on the local time zone of your session (which defaults to the time zone of the server if not specified otherwise.)
Switch both to timestamptz, or timestamp if time zones are completely irrelevant to you. (If in doubt, use timestamptz.)
Not the cause of your problem, but your query should probably be:
SELECT id, text, category, starttime, endtime, creation
FROM entries
WHERE starttime >= timestamp '2013-03-21' -- defaults to 00:00 time
AND starttime < timestamp '2013-03-22'
ORDER BY id
a BETWEEN x AND y is almost always wrong for timestamp types due to fractional numbers! What would your query do with starttime = '2013-03-21T23:59:59.123+00'?
Unexpected result when comparing dates
MySQLs implicit type conversion can be very surprising. If you want to understand the behavior of your queries, you can try to apply the type conversion rules as described in Type Conversion in Expression Evaluation. However - I failed to do that for your case. For example: For the two expressions date > '2019' and date > 2019 I would apply the following rule:
If one of the arguments is a
TIMESTAMPorDATETIMEcolumn and the
other argument is a constant, the constant is converted to a timestamp
before the comparison is performed.
But that cannot be the case, because neither the number 2019 nor the string '2019' can be converted to a temporal type. Here is a query, which demonstrates some implicit conversions:
select '2019' + interval 0 day -- implicit cast to date(time)
, 2019 + interval 0 day
, 20190101 + interval 0 day
, 190101 + interval 0 day
, '2019*01*01' + interval 0 day
, '2019-01-01' + interval 0 day
, '2019-01-01' + 0 -- implicit cast to numeric
, date('2019-01-01') + 0
, date('2018-01-01') > 2019
, date('2018-01-01') > '2019'
;
Result:
Expression | Result
------------------------------|-----------
'2019' + interval 0 day | null
2019 + interval 0 day | null
20190101 + interval 0 day | 2019-01-01
190101 + interval 0 day | 2019-01-01
'2019*01*01' + interval 0 day | 2019-01-01
'2019-01-01' + interval 0 day | 2019-01-01
'2019-01-01' + 0 | 2019
date('2019-01-01') + 0 | 20190101
date('2018-01-01') > 2019 | 1
date('2018-01-01') > '2019' | 0
As you see, when we try to convert 2019 or '2019' to a date (or datetime), we get NULL. Thus the conditions should also be evaluated to NULL and the result set would be empty. But as we know, that is not the case. Maybe I'm just wrong, assuming that 2019 and '2019' are constants. But then I don't know what they could mean.
So I can only make assumptions. And my assumtion is: Whenever one comparator is numeric, the other value is also converted to a numeric value. This would be the case for date > 2019 aswell as for date > year(@THIS_YEAR). In this case the date 2018-01-01 is converted to 20180101 (see the table above), which (in numeric context) is greater than 2019. So you get rows from the year 2018.
For date > '2019' I can only assume, that the values are compared as strings. And '2018-01-01' as string is considered "smaller" than 2019.
But even if that behavior would be properly documented, the rules are too difficult to remember, because one can hardly see any logic behind them. (I don't say - there is no logic - I just don't see any.)
So I can give you one advise: If you want to compare two incompatible types, always cast or convert them to be compatible.
WHERE year(date) >= year(@THIS_YEAR)
would be fine, since you compare two numeric values. But that is not necessery in your case and you can just use
WHERE date >= @THIS_YEAR
because 2019-01-01 00:00:00 in
`SET @THIS_YEAR = "2019-01-01 00:00:00";`
is a perfectly formatted DATETIME string and can be considered compatible with the DATETIME type. '2019-01-01' would be just fine aswell.
Note that if you wrapp a column into a function call (like year(date)) you will loose the ability to use an index on that column.
Dynamic SQL query returning unexpected results when comparing dates
This is in depth, but it's very informative: http://www.sommarskog.se/dyn-search.html
SET @sql = N'SELECT e.Id, e.FirstName, e.LastName, v.ScheduledDate
FROM Employee e, Visit v
WHERE v.EmpId = e.Id'
-- Several IF IS NOT NULL statements here
IF @d IS NOT NULL
SET @sql = @sql + N' AND (v.ScheduledDate >= @date AND v.ScheduledDate < @date + 1)'
-- This stays the same, EVEN if the parameter is NULL and not used
-- This ensures execution plan re-use is available
SET @param_definition = '@date DATETIME, -- Or whatever type v.ScheduledDate is
@smeg INT,
@head WHATEVER'
SP_EXECUTESQL
@sql,
@param_definition,
@date = CAST(@d AS DATE),
@smeg = 0,
@head = NULL
Unexpected query results in Oracle db
It seems the optimizer gets confused with so many levels of obfuscating the join condition.
The first query results in the following execution plan:
SQL_ID 9k6g3m0xs31w7, child number 1
-------------------------------------
select t1.*, nvl(t2.date1, timestamp'1900-01-01 00:00:00') from table1
t1 left join table1 t2 on 1 = 0 where t1.date1 > nvl(t2.date1,
timestamp'1900-01-01 00:00:00')
Plan hash value: 963482612
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| 2 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| TABLE1 | 1 | 2 | 26 | 3 (0)| 2 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F7AF7B7D / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."DATE1">TIMESTAMP' 1900-01-01 00:00:00.000000000')
So the planner correctly sees that the self join is unnecessary and replaces the NVL() condition on the joined table with a condition on the column itself.
Apparently this "replacing" the condition does not work correctly in 12.2.
The second query results in the following plan:
SQL_ID 3twykk3kcyyxy, child number 1
-------------------------------------
select t1.*, nvl(t2.date1, timestamp'1900-01-01 00:00:00') from table1
t1 left join table1 t2 on t1.date1 || '---' = '-' where t1.date1 >
nvl(t2.date1, timestamp'1900-01-01 00:00:00')
Plan hash value: 736255932
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| 0 |00:00:00.01 | 7 | | | |
|* 1 | FILTER | | 1 | | | | 0 |00:00:00.01 | 7 | | | |
| 2 | MERGE JOIN OUTER | | 1 | 1 | 26 | 8 (25)| 2 |00:00:00.01 | 7 | | | |
| 3 | SORT JOIN | | 1 | 2 | 26 | 4 (25)| 2 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS FULL | TABLE1 | 1 | 2 | 26 | 3 (0)| 2 |00:00:00.01 | 7 | | | |
|* 5 | SORT JOIN | | 2 | 2 | 26 | 4 (25)| 0 |00:00:00.01 | 0 | 1024 | 1024 | |
| 6 | VIEW | VW_LAT_C83A7ED5 | 2 | 2 | 26 | 3 (0)| 0 |00:00:00.01 | 0 | | | |
|* 7 | FILTER | | 2 | | | | 0 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS FULL| TABLE1 | 0 | 2 | 26 | 3 (0)| 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F7AF7B7D
4 - SEL$F7AF7B7D / T1@SEL$1
6 - SEL$BCD4421C / VW_LAT_AE9E49E8@SEL$AE9E49E8
7 - SEL$BCD4421C
8 - SEL$BCD4421C / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."DATE1">NVL("ITEM_1",TIMESTAMP' 1900-01-01 00:00:00.000000000'))
5 - access(INTERNAL_FUNCTION("T1"."DATE1")>NVL("ITEM_1",TIMESTAMP' 1900-01-01 00:00:00.000000000'))
7 - filter(INTERNAL_FUNCTION("T1"."DATE1")||'---'='-')
So the optimizer replaced the reference to the table column with some ITEM1 placeholder - and the step access(INTERNAL_FUNCTION("T1"."DATE1")>NVL("ITEM_1",TIMESTAMP' 1900-01-01 00:00:00.000000000')) messes things up.
With 12.1 the plan is essentially the same, the only difference is that the access() part is missing in the predicates, so I guess that replacement is somewhat buggy in 12.2 (to be precise my version is: 12.2.0.1.0)
Adding fixed time onto datetime gives unexpected results
i get back a seemingly valid datetime : 2017-01-01 06:00:00.000
You're not.
You're getting back a date that has been automatically cast to a string, and have glued another string on the end, giving you a string that looks like a datetime.
If you want to add something to the date, use another dateadd(). This will give you a BETWEEN comparison with actual datetimes.
Right now you are doing a "between" with a datetime and a string.
I'm surprised it doesn't throw an error.
MySQL query returning unexpected results
I think you should join your subset by updateAT without DATE function
SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a
JOIN cities AS b ON a.cid = b.cid
JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt FROM ci_scores
GROUP BY DATE(updatedAt)) AS L ON L.maxUpdatedAt = a.updatedAt
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;
UPDATE:
There is another error that in the sub query you need to group by the city id as well so that you can get the correct max date:
SELECT a.cid, b.name, a.score, a.updatedAt FROM ci_scores AS a
JOIN cities AS b ON a.cid = b.cid
JOIN (SELECT MAX(updatedAt), cid AS maxUpdatedAt FROM ci_scores
GROUP BY DATE(updatedAt), cid) AS L ON L.maxUpdatedAt = a.updatedAt AND L.cid = a.cid
WHERE a.cid = 10158241 ORDER BY a.updatedAt ASC;
Related Topics
Rails .Where() Query Not Working
How to Generate Date Series to Occupy Absent Dates in Google Biqquery
Why Is 30 the Default Length for Varchar When Using Cast
How to Pass Table Name as a Parameter in Oracle
Returning Result Even for Elements in In List That Don't Exist in Table
How to Drop a Foreign Key Constraint Only If It Exists in SQL Server
SQL Server Stored Procedure Return a Table
Need a Row Count After Select Statement: What's the Optimal SQL Approach
Is Inner Join the Same as Equi-Join
What Is the SQL Command to Return the Field Names of a Table
What Are the Pros/Cons of Using a Synonym VS. a View
SQL Azure - Copy Table Between Databases
How to Insert Identity Manually