Subtract hours from the now() function
Answer for timestamp
You need to understand the nature of the data types timestamp
(timestamp without time zone
) and timestamptz
(timestamp with time zone
). If you don't, read this first:
- Ignoring time zones altogether in Rails and PostgreSQL
The AT TIME ZONE
construct transforms a timestamp
to timestamptz
, which is almost certainly the wrong move for your case:
where eventtime at time zone 'CET' between '2015-06-16 06:00:00'
and '2015-06-17 06:00:00'
First, it kills performance. Applying AT TIME ZONE
to the column eventtime
makes the expression not sargable. Postgres cannot use plain indexes on eventtime
. But even without index, sargable expressions are cheaper. Adjust filter values instead of manipulating every row value.
You could compensate with a matching expression index, but it's probably just a misunderstanding and wrong anyway.
What happens in that expression?
AT TIME ZONE 'CET'
transforms thetimestamp
valueeventtime
totimestamptz
by appending the time offset of your current time zone. When using a time zone name (not a numeric offset or an abbreviation), this also takes DST rules (daylight saving time) into account, so you get a different offset for "winter" timestamps. Basically you get the answer to the question:What's corresponding UTC timestamp for the given timestamp in the given time zone?
When displaying the result to the user it is formatted as local timestamp with the according time offset for the current time zone of the session. (May or may not be the same as the one used in the expression).
The string literals on the right side have no data type to them, so the type is derived from the assignment in the expression. Since that's
timestamptz
now, both are cast totimestamptz
, assuming the current time zone of the session.What's the corresponding UTC timestamp for the given timestamp for the time zone setting of the current session.
The offset can vary with DST rules.
Long story short, if you always operate with the same time zone: CET
or 'Europe/Berlin'
- same thing for present-day timestamps, but not for historic or (possibly) future ones, you can just cut the cruft.
The second problem with the expression: is almost always wrong with BETWEEN
timestamp
values. See:
- Optimize BETWEEN date statement
- Find overlapping date ranges in PostgreSQL
SELECT date_trunc('hour', eventtime) AS hour
, count(DISTINCT serialnumber) AS ct -- sure you need distinct?
FROM t_el_eventlog
WHERE eventtime >= now()::date - interval '18 hours'
AND eventtime < now()::date + interval '6 hours'
AND sourceid = 44 -- don't quote the numeric literal
GROUP BY 1
ORDER BY 1;
now()
is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP
. Both return timestamptz
(not timestamp
!). You can use either.now()::date
is equivalent to CURRENT_DATE
. Both depend on the current time zone setting.
You should have an index of the form:
CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime)
Or, to allow index-only scans:
CREATE INDEX foo2 ON t_el_eventlog(sourceid, eventtime, serialnumber)
If you operate in different time zones, things get more complicated and you should use timestamptz
for everything.
Alternative for timestamptz
Before the question update, it seemed like time zones matter. When dealing with different time zones, "today" is a functional dependency of the current time zone. People tend to forget that.
To just work with the current time zone setting of the session, use the same query as above. If executed in a different time zone, the results are wrong in actuality. (Applies to the above as well.)
To guarantee a correct result for a given time zone ('Europe/Berlin' in your case) irregardless of the current time zone setting of the session, use this expression instead:
((now() AT TIME ZONE 'Europe/Berlin')::date - interval '18 hours')
AT TIME ZONE 'Europe/Berlin' -- 2nd time to convert back
Be aware that the AT TIME ZONE
construct returns timestamp
for timestamptz
input and vice-versa.
As mentioned at the outset, all the gory details here:
- Ignoring time zones altogether in Rails and PostgreSQL
How to Subtract Hours from Now() in VB .NET
DateTime.Now.AddHours(-9)
Check out http://msdn.microsoft.com/en-us/library/system.datetime(v=vs.110).aspx for a list of methods.
Subtract hours from current date time and compare to column value whose data type is datetime
How about DateDiff()
where
DateDiff( hour, ReturnDT, getdate() ) > 1
Subtract a specified number of hours from a given date/time to get a new date/time
You can subtract n hours from a date by simply subtracting n/24
from it. You can also use the TimeSerial
function to subtract hours, minutes and seconds. This example illustrates the two methods, it subtracts 1 hour and a half from the current time using the two methods.
Sub substractDates()
Dim d1 As Date, d2 As Date, d3 as Date
d1 = Now()
d2 = d1 - TimeSerial(1, 30, 0)
d3 = d1 - 1.5 / 24
Debug.Print d1, d2, d3
End Sub
p.s. yet a third way is to use TimeValue("1:30:0")
which is equivalent to TimeSerial(1, 30, 0)
.
How to subtract some Hours from current Time in AngularJS
You can subtract minutes and hours in javascript from the Date() object.
function Ctrl($scope)
{
var now = new Date();
now.setHours(now.getHours()-4);
now.setMinutes(now.getMinutes()-59);
$scope.ProductionDate = now;
}
Subtract number of hours from current time in Java
tl;dr
ZonedDateTime
.now(
ZoneId.of( "Asia/Tokyo" )
)
.minusHours(
Integer.parseInt( "54:34:41".split( ":" )[0] )
)
Details
Parse hours
Get the number of hours.
int hours = Integer.parseInt( "54:34:41".split( ":" )[0] ) ;
ISO 8601
Your input text for a span-of-time does not comply with the ISO 8601 standard for date-time values. The java.time classes by default use the standard formats when parsing/generating text.
If instead of 54:34:41
you had PT54H34M41S
, then we could use:
int hours = Duration.parse( "PT54H34M41S" ).toHours() ;
I recommend you stick with the standard format rather than the ambiguous clock-time format.
Capture current moment
Capture the current moment as seen in a particular time zone.
ZoneId z = ZoneId.of( "Africa/Casablanca" ) ;
ZonedDateTime zdt = ZonedDateTime.now( z ) ;
Subtract hours
Subtract your hours.
ZonedDateTime earlier = zdt.minusHours( hours ) )
Subtracting hours from date string
You can simply use moment subtract
method.
Here a working sample:
// Current time in Shanghai
var m = moment.tz('Asia/Shanghai');
console.log(m.format('Y-M-D HH:mm:ss'));
// Subtract 9940 hours
console.log(m.subtract(9940, 'hours').format('Y-M-D HH:mm:ss'));
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment-timezone/0.5.7/moment-timezone-with-data-2010-2020.min.js"></script>
Related Topics
Rodbc SQLquery() Returns Varchar(255) When It Should Return Varchar(Max)
How to Set a Default Row for a Query That Returns No Rows
Safest Way to Get Last Record Id from a Table
SQL Server Row_Number() on SQL Server 2000
SQL Convert Week Number to Date (Dd/Mm)
How to Store Decimal Values in SQL Server
SQL Server - Where Is "Sys.Functions"
How to Delete Rows in Tables That Contain Foreign Keys to Other Tables
Connect by Clause in Regex_Substr
How to Read Multiple Result Sets Returned from a SQL Server Stored Procedure in R
How to Compare Two Columns for Equality in SQL Server
SQL Server Variable Scope in a Stored Procedure
How to Dynamically Use Tg_Table_Name in Postgresql 8.2
Join a Count Query on Generate_Series() and Retrieve Null Values as '0'
How to Get the Next Number in a Sequence