I may sound silly asking it – but I have the need to add a value derived from one column of database to the other which is the date. Following is the image of data I have – timeelapsed/60000 gives me the duration of task in minutes and I would require the same to be deducted from the lastupdatedti…
Tag: time
Get number of days between two dates in SQL
Fleet_ID State ODO_Reading Date_Added 1 F1 State 1 42000 10/01/2021 2 F1 State 1 43100 14/03/2021 3 F1 State 1 43900 26/04/2021 4 F2 State 1 63010 22/02/2021 5 F2 State 1 70800 06/04/2021 6 F3 State 2 17000 03/01/2021 7 F3 State 2 18560 19/03/2021 8 F3 State 2 19695 23/04/2021 Above is a sample database Is it…
SQL (HUE) : Is there any way to convert 24 hrs time into 12 hrs AM / PM format with hours buckets
I have table A which contains column time stored as timestamp datatype. Table A: Contains time column in HH:MM:SS in 24 hrs format. Answer Please use below code. Replace now() with time for your query. Explanation – firstly i am checking if hour is >12. If yes, deducting 12 to get the hour. Then sett…
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…
SQLite Incomplete Time String
I have a table with incomplete time strings, such as ‘0:09.735’ (minutes:seconds.milliseconds). As the function strftime expects certain formats, such as ‘%H:%M:%f’, I have “to complete” the incomplete timestring first. The first strftime function with the complete timestri…
How to calculate employees working hours in sql
My company business hour is 7:30 to 18 and 13:00 to 15:00 is lunch time. Important part is about lunch time that should not calculate as working time of employee at all. So imagine employee start working at 8:30 and exit at 15:00 so the time of 4:30 hours should be calculate for him. actually I want to remove…
SQL Server – Aggregate data by minute over multiple days
Context I’m using Microsoft SQL Server 2016. There is a database table “Raw_data”, that contains the status of a machine, together with it’s starting time. There are several machines and …
Check datatype of column after applying function MySQL
I want to know datatype of column after using a function. After using timediff function what datatype is it? I’ve looked at the doc to search for output datatype of a function https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html but could not find, Also it would be inefficient to memoriz…
Postgres converting double double precision to text creates ‘1.50000000000’
Working with postgres and I need to convert total minutes into the format of 1h 1m. I’m doing this via the following code replace( (119 / 60 + (119 % 60) / 100.0)::text, ‘.’, ‘h ‘ ) + …
setting time range in SQL Developer
I am working on a dataset that contains car accidents and their time of occurrence. (the data set exists in SQL Server under the name accident). I have a column that is in date format. I would like to extract the time from the column. Then add a new column called lightining_period label the time as daytime or…