Menu
Below is the query I am running to get updates in last one hour.
select count(*)
from my_table
where last_updated_date between to_date(to_char(sysdate,'YYYY-MM-DD HH24'))-1/24 and to_date(to_char(sysdate,'YYYY-MM-DD HH24'));
Our DB is oracle and it is failing with
ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
What is the reason for this failure?
914
Here is the SQL to show latest time using now() function. Here is the SQL to get last 1 hour data in MySQL. In the above query, we select only those rows whose order_date falls within past 1 hour interval. We use INTERVAL clause to easily substract 1 hour interval from present time obtained using now() function.
For example, if your database is in London but you are in New York, the SYSTIMESTAMP will return a time that is 5 hours ahead of your time. It's similar to the SYSDATE function, however it returns a TIMESTAMP WITH TIME ZONE instead of a DATE data type. This means it includes the date, time, and fractional seconds.
SYSDATE will give the system date of the server. CURRENT_DATE will give you the system date of the session. For example, if your oracle server is in UK, the SYSDATE will be the UK time whereas, the CURRENT_DATE will be the Indian time.
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE .
just Subtract 1/24 from sysdate to get the time of 1 hour ago
select count(*) from my_table where last_updated_date >= (sysdate-1/24)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
