I want to create an array constructed from two timestamps. After that, use this as a range to find the difference between the first element and the second element. The goal is to find the time difference between timestamps within business hours (9 am to 17 pm). What should be the right approach here? Can I cr…
Tag: datetime
SQL : Get the duration between start DateTime and End DateTime from Table entries
I have a SQL table which has entries of machine start and end time (DateTime) with duration of machine running. Given a start DateTime and End DateTime I want to calculate the duration for which machine was running. DB is SQL and Code is in C#. Note: THere might be some duration when the machine is not runnin…
How can I filter records in an SQLite database against a DateTime value that was not supplied?
I have an SQLite database of TimeRecords. When a new record is created, no EndTime is provided. I want to retrieve the first record where the EndTime was not supplied and assign it to a new TimeRecord EndTime is stored as a DateTime variable. My Current Query Calling the Query I have done some research and di…
How to find the most appropriate date between overlapping date periods in mysql
I`w got an 2 tables with such data: Table 1 id s_id s_date 1 33 2021-03-05 10:36:59 2 33 2021-03-06 10:36:59 3 33 2021-03-09 10:36:59 4 33 2021-03-10 13:36:59 5 33 2021-03-11 12:36:59 6 33 2021-…
Set minutes of timestamp to a specific value
What is the easiest way to set the exact minutes value of a timestamp? This only adds minutes instead of setting the exact value: Answer Use date_trunc() before you add 2 minutes: Or, to retain seconds and sub-seconds: Demo: Should be substantially faster than manipulating the text representation and casting …
SQL date scenario for getting previous month’s data to-date on the 1st of month, then drop last month
I’m trying to recreate an existing View in Snowflake (originally in SQL Server). I need to get last month’s data which is easy enough, but here’s the scenario: On 3-1-21, it should retrieve 2-1-21 to 3-1-21, but then starting on 3-2-21, it should only show 3-1-21 to-date, as our month end re…
MySQL CURDATE() + INTERVAL 1 DAY not returning as should
I am stumped! I have a table that has a field job_date_start — And the DataType is set to DATETIME If I SELECT job_date_start FROM table; from my table the output is: +—————————+ |…
Coming up with logic to be able to add a time interval if the same date exists for the same clip#
I am trying to separate out a clip if it has the same date. For example, since “Clip Number” 1 and 2 has the same date so I am trying to make the date different by adding a 6 hour interval. I would like to add a new field that is similar to the column “New Date” Date Clip_Number New
How to add/subtract n number of days from a custom date in postgresql?
I have a database in which, I have a issue_date column, and forecast_date column, I am selecting the maximum date from the database, but I want to fetch/query/extract the N th previous day from maximum available date like (maximum date – 1 / 2 or n number of days). & As the max date is custom, so ca…
How to read from the two same dates without input the time
I have a query like below. When I submit a date like the following patients.created_at BETWEEN ‘2018-05-22’ AND ‘2018-05-22’ it doesn’t return anything but if I enter BETWEEN ‘2018-05-22’ patients.created_at ‘AND’ 2018-05-23′ it returns a value. I th…