convert milliseconds to seconds in sql server
You're a star SSC Rookie, completely overlooked the TIME data type. Click to share on Twitter (Opens in new window), Click to share on Facebook (Opens in new window), Click to share on LinkedIn (Opens in new window), Click to share on Tumblr (Opens in new window), Click to share on Reddit (Opens in new window). It only takes a minute to sign up. It mentions something about "datepart . These last few days I'm a little out of time, but to keep up with updating here I will do another quick post demonstrating how to convert milliseconds, seconds or minutes to TIME in SQL Server. Milliseconds can be preceded by either a colon (:) or a period (.). Dirceu Resende 2023. As CPU is in milliseconds.From the beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6 of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3 of a second). 2135. . For this reason, I decided to create this post to help with this question. When the fractional seconds precision of the time(n) value is greater than the precision of the datetimeoffset(n) value, the value is rounded up to fit. This forum has migrated to Microsoft Q&A. Enter your email address to subscribe to this blog and receive notifications of new publications by email. Improves MS SQL Database Performance, declare @s int = @ms/1000 -- time in seconds (rounded), decare @min int = @s/60 -- time in minutes (rounded), For every expert, there is an equal and opposite expert. SELECT TOP (10) SUBSTRING (EST.TEXT, (EQS.statement_start_offset / 2) + 1, ((CASE EQS.statement_end_offset WHEN -1 THEN DATALENGTH (EST.TEXT) ELSE EQS.statement_end_offset END - EQS.statement_start_offset) / 2) + 1) AS Command, EQS.total_worker_time, CONVERT (VARCHAR (8), DATEADD (MILLISECOND, EQS.min_worker_time + 86400000, 0), 114) AS CPU_time, ECP.query_plan FROM sys.dm_exec_query_stats EQS CROSS APPLY sys.dm_exec_sql_text (EQS.sql_handle) EST CROSS APPLY sys.dm_exec_query_plan (EQS.plan_handle) ECP ORDER BY EQS.total_worker_time DESC. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, Microsoft SQL databse I tried this: SELECT (DATEPART(hh,'12:13:14.123') * 60 * 60 * 1000) SELECT (DATEPART(n,'12:13:14.123') * 60 * 1000) SELECT (DATEPART(s,'12:13:14.123') * 1000) SELECT DATEPART(ms,'12:13:14.123'), And this is for which dbms product? Why are players required to record the moves in World Championship Classical games? Given below is the simple script to convert itwithout doing any divisions and remainders,using only built-in SQL functionsDateAdd,DatePart,DateDiff, Result Are there any canonical examples of the Prime Directive being broken that aren't shown on screen? Making statements based on opinion; back them up with references or personal experience. Genesis & History. You pay the cost of computation at query time, which is the same as a non-persisted computed column. There is no issues doing this. -- Converter 5874502 Milisegundos para tempo, -- Converter 5874502 Milisegundos para string, How to convert numbers to roman numerals in SQL Server, Identifying tables with IDENTITY columns in SQL Server, Live on @cloud4developers Azure SQL straight to the point. Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. Simple deform modifier is deforming my object. The time component is set to 00:00:00.000. Then use 1 as the second parameter, for the number of milliseconds to add.. Try again later. . 565), Improving the copy in the close modal and post notices - 2023 edition, New blog post from our CEO Prashanth: Community is the future of AI. I want to convert it in sql to milliseconds. The default string literal format (used for down-level client) will align with the SQL standard form, which is defined as hh:mm:ss[.nnnnnnn]. If a colon is used, the number means thousandths-of-a-second. 2140. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); This site uses Akismet to reduce spam. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. Internally to SQL Server in this DMV (sys.dm_exec_query_stats) CPU time is measured in microseconds and the report is pulling the usage per second.However, the ms in the report is in milliseconds for the Activity Monitor and microsecond would be mcs.. UPDATE MyTable SET NewIntColumn = DATEDIFF (SECOND, '19000101', MyDateTimeColumn) 19000101 is the SQL Server epoch. SELECT CONVERT(CHAR(12),@EndDate-@StartDate,114). Note this script will work in sql Server 2008 and above as here I am using TIME datatype and SYSDATETIME() functions which were introduced in Sql Server 2008. Short story about swapping bodies as a job; the person who hires the main character misuses his body. It shows the following columns: DatabaseName:The name of the database where the query is running. Browse other questions tagged, Start here for a quick overview of the site, Detailed answers to any questions you might have, Discuss the workings and policies of this site. You can also convert milliseconds to date & time and the other way around. Making statements based on opinion; back them up with references or personal experience. "Signpost" puzzle from Tatham's collection, Finding the distance from a corner of a cube to the midpoint of an edge. declare @s int = @ms/1000 -- time in seconds (rounded) decare @min int = @s/60 -- time in minutes (rounded) For every expert, there is an equal and opposite expert. This is the ratio of CPU time used by that query per second. What differentiates living as mere roommates from living in a marriage-like relationship? For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet. To learn more, see our tips on writing great answers. SQL Server experts to answer whatever question you can come up with. MS SQL Consultants: Time to put both the "Time" and the "Date" parts together. Image of minimal degree representation of quasisimple group unique up to conjugacy. (@milli - ((@milli/3600000)*3600000))/60000 as minutes. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. Find centralized, trusted content and collaborate around the technologies you use most. These last few days I'm a little out of time, but to keep up with updating here I will do another quick post demonstrating how to convert milliseconds, seconds or minutes to TIME in SQL Server. I have documented my personal experience on this blog. Learn more about Stack Overflow the company, and our products. It's not them. of seconds.DateDiff_Big (which returns a BigInt) is not available prior to SQL Server 2016. and also want to produce a column like avarage. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. Somebody help me out in this iF theRe iS a wAy iN tHen theRe iS a wAy oUt.. Learn how your comment data is processed. The following example compares the results of casting a string to each date and time data type. When the conversion is to datetime, hour, minute, and second values are copied; and the date component is set to '1900-01-01'. Did the drapes in old theatres actually say "ASBESTOS" on them? How are engines numbered on Starship and Super Heavy? When the conversion is to time(n), the hour, minute, and seconds are copied. Using hour 24 to represent midnight and leap second over 59 as defined by ISO 8601 (5.3.2 and 5.3) are not supported to be backward compatible and consistent with the existing date and time types. Reset identity seed after deleting records in SQL Server, Folder's list view has different sized fonts in different folders, User without create permission can create a custom object from Managed package using Custom Rest API. Finding the distance from a corner of a cube to the midpoint of an edge. You can add a new column and manually update it as @gbn suggested, but now you have to constantly keep this column up to date with insert/update triggers or some other mechanism. Is it safe to publish research papers in cooperation with Russian academics? The table is static, Won't be updating or deleting data. the Allied commanders were appalled to learn that 300 glider troops had drowned at sea, xcolor: How to get the complementary color, Passing negative parameters to a wolframscript, Folder's list view has different sized fonts in different folders. Something like this: Invoke-sqlcmd -ServerInstance .I -Query "SELECT * The third column shows the value that will be stored in the database table column. Using DMV-sys.dm_exec_query_stats to validate CPU utilization time for running queries I would like to convert the min_worker_time field that is reported to MICROSECONDS as per the documentation with your script provided above, however, I realize the time for some queries If the CPU time is above acceptable, can I convert microseconds like this as below? Content Discovery initiative April 13 update: Related questions using a Review our technical responses for the 2023 Developer Survey, Add a column with a default value to an existing table in SQL Server, How to concatenate text from multiple rows into a single text string in SQL Server. And if your total duration goes above 1 day, you can use this to get the days and hr:min:sec:ms separately. For conversion from datetime or smalldatetime to character data, see the previous table for the output format.. 5 Hijri is a calendar system with several variations. 6 For a milliseconds (mmm) value of 0, the millisecond decimal fraction value won't . . Login to reply. What differentiates living as mere roommates from living in a marriage-like relationship? Does the order of validations and MAC with clear text matter? The opinions expressed here represent my own and not those of my employer. If the conversion is to datetimeoffset(n), the date is set to '1900-01-01', and the time is copied. Asking for help, clarification, or responding to other answers. Constraint Violating Yak Guru, visakh16 Micro-Seconds So, I tried to create query below, of course by taking help from internet, so that I can have a sample code handy for future reference: SQL with Manoj. The following table shows the valid string literal formats for the time data type. Text: The text of the current statement being run. . How to get Time from DateTime format in SQL? To subscribe to this RSS feed, copy and paste this URL into your RSS reader. Please start any new threads on our new When the conversion is to smalldatetime, the date is set to '1900-01-01', and the hour and minute values are rounded up. To learn more, see our tips on writing great answers. Your email address will not be published. Click on the banner to learn about and purchase my database training on Azure. Specifies the number of digits for the fractional part of the seconds. I know the OP's only asking about "time" durations, but for what it's worth, the pattern in @Lingo's solution scales better to durations >= 1 day than @t-clausen.dk's, because DateDiff returns an Int, which means it tops out at ~24 days worth of milliseconds and ~67 yrs. This site provides the current time in milliseconds elapsed since the UNIX epoch (Jan 1, 1970) as well as in other common formats including local / UTC time comparisons. To learn more, see our tips on writing great answers. How do the interferometers on the drag-free satellite LISA receive power without altering their geodesic trajectory? rev2023.5.1.43405. In this case, I chose milliseconds as the input for several reasons: Milliseconds are the lowest common denominator for time measurement in SQL Server, so the conversion process is always the same. Convert a datetime column to number of seconds, How a top-ranked engineering school reimagined CS curriculum (Ep. Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. I started with the simplistic expression DATEDIFF(SECOND, startDateTime, endDateTime) and assumed that it might return 0 seconds for up to 499 (in fact due to precision 497) milliseconds and round up to 1 second for values starting at 500 milliseconds. Change is inevitable Change for the better is not. Which reverse polarity protection is better and why? How to convert a sequence of integers into a monomial SELECT GETDATE() 'Today', CAST(SYSDATETIME() AS TIME) 'hh:mm:ss.nnnnnnn' RESULT: 2. When you convert to date and time data types, SQL Server rejects all values it cannot recognize as . Thanks for contributing an answer to Database Administrators Stack Exchange! Convert Varchar To Datetime and Add Seconds, Folder's list view has different sized fonts in different folders. I read the MSDN Documentation but didn't get any wiser. When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. 5 bytes, fixed, is the default with the default of 100ns fractional second precision. Comments left by any independent reader are the sole responsibility of that person. The following code shows the results of converting a date value to a datetime value. In the example above, the expression converts datetime value to string and extracts last digits (millisecons). Large scale of database and data cleansing Why does Acts not mention the deaths of Peter and Paul? how do i convert milliseconds to seconds or minutes. The TIME part of the input string is used. You can use 19700101 for Unix epoch for example. Find centralized, trusted content and collaborate around the technologies you use most. Is "I didn't think it was serious" usually a good defence against "duty to rescue"? SQL Server, SQL Queries, DB concepts, Azure, Spark SQL . Thanks for contributing an answer to Stack Overflow! I want to convert this value to the corresponding "hh:mm:ss" format. What do hollow blue circles with a dot mean on the World Map? DATETIME is used to store an instant ("point in time") at a particular granularity (approx 300 milliseconds IIRC). When the destination precision is less than the source precision, the fractional seconds is rounded up to fit the destination precision. Not the answer you're looking for? The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Required fields are marked *. Here is an example, grabbing the current time into a variable, and then adding one millisecond to it and saving the result as a second variable, and then printing each . Hours : Minutes : Seconds . I want to convert microseconds in to seconds in SQL Server. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, How a top-ranked engineering school reimagined CS curriculum (Ep. When only the hour is specified, all other values are 0. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem. Azure SQL Database It's too big for DATEADD which only accepts an int. Convert a Unix timestamp to time in JavaScript. February 25, 2009 at 1:01 am #128026 . Since the above calculations are not overly complex, just include the calculation in your query. Note that this code will not work if you declare @d as DATETIME or . By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. PM cannot be specified when the hour equals 0. hh is two digits, ranging from 0 to 23, that represent the number of hours in the time zone offset. Can you still use Commanders Strike if the only attack available to forego is an attack against an ally? Embedded hyperlinks in a thesis or research paper. What is a good way to create a new column that represents the long value for the datetime column? Rounding milliseconds SQL Server 2000. What is the symbol (which looks similar to an equals sign) called? CONVERT(TIME,DATEADD (ms, @YOURMILLISECONDS, 0)). Applies to: How do I UPDATE from a SELECT in SQL Server? By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. Is there any known 80-bit collision attack? SQL Server 2005; T-SQL (SS2K5) . Recently, I received a request inquiring how to convert Milliseconds to Days, Hours, Minutes, Seconds & Milliseconds. The hour value of 0 represents the hour after midnight (AM), regardless of whether AM is specified. Your email address will not be published. SQL Error - "SELECT TOP 100" throws error in SparkSQL - what's the correct syntax? By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. I got it, it isn't the nice way but it works: Thanks for contributing an answer to Stack Overflow! You can add a new column and manually update it as @gbn suggested, but now you have to constantly keep this column up to date with insert/update triggers or some other mechanism. Is it safe to publish research papers in cooperation with Russian academics? hh is two digits, ranging from 0 to 23, that represent the hour. SQL. Azure SQL Managed Instance For Informatica, the maximum is 12 (hh:mm:ss.nnn). It's a matter of cast and string concat if you . Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, Converting Milliseconds to Days, hours, minutes and seconds, How a top-ranked engineering school reimagined CS curriculum (Ep. When AM or PM is specified, the time is stored in 24-hour format without the literal AM or PM. For calculating the time difference, directly subtract two dates from each other as, kshitij kumarkshitij@krayknot.comwww.krayknot.com. Break it into two parts: seconds, then milliseconds. 3 Input when you convert to datetime; output when you convert to character data.. 4 Designed for XML use. As a developer there are times when yo need to convert seconds to minutes, hours and days. Making statements based on opinion; back them up with references or personal experience. So, I tried to create query below, of course by taking help from internet, so that I can have a sample code handy for future reference: Your email address will not be published. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. Image of minimal degree representation of quasisimple group unique up to conjugacy. Related. I have this time-duration: 00:00:23.323 (@milli - (((@milli)/60000)*60000)) /1000 as seconds, @milli-(((@milli)/1000)*1000) as milliseconds, SELECT CONVERT (CHAR(13), DATEADD (ms, @ms, '01/01/00'), 14), Obviously this would be the maximum number that it would show after this you would need to handle days - Remember that milliseconds are displayed and rounded to 1/300 of a second, ,(@Mili%(1000*60*60))/(1000*60)as Minutes, ,((@Mili%(1000*60*60))%(1000*60))/1000 as Seconds, ,((@Mili%(1000*60*60))%(1000*60))%1000 as MiliSecs. Create a free website or blog at WordPress.com. DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS). If the conversion is to date, the conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time". Is there such a thing as "right to be heard" by the authorities? In Informatica, the default is 4 bytes, fixed, with the default of 1ms fractional second precision. Otherwise, a runtime error is raised. To learn more, see our tips on writing great answers. 565), Improving the copy in the close modal and post notices - 2023 edition, New blog post from our CEO Prashanth: Community is the future of AI. Create a new column (ALTER TABLE) then run an UPDATE on it, 19000101 is the SQL Server epoch. Why the obscure but specific description of Jane Doe II in the original complaint for Westenbroek v. Kappa Kappa Gamma Fraternity? rev2023.5.1.43405. 1. like this. In my earlier article, I wrote the solutionShorter way to convert Seconds to Days, Hours, Minutes, Seconds. EDIT:// By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. More info about Internet Explorer and Microsoft Edge, Backward Compatibility for Down-level Clients. The time zone offset is set to +00:00. The optional time zone difference (TZD) is allowed in the input but is not stored. The following table shows the type mapping between an up-level instance of SQL Server and down-level clients. To comply with ISO 8601, use 24-hour format, not AM or PM. The long would represent a number of seconds. Enter your email address to follow this blog and receive notifications of new posts by email. Connect and share knowledge within a single location that is structured and easy to search. It's not them. An error has occurred; the feed is probably down. Arithmetic overflow error converting expression to data type int. how to convert milliseconds,seconds,minutes,hours into days in sql server 2005? document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); Enter your email address to follow this blog and receive notifications of new posts by email. Analytics Platform System (PDW). Why the obscure but specific description of Jane Doe II in the original complaint for Westenbroek v. Kappa Kappa Gamma Fraternity? This section describes what occurs when other date and time data types are converted to the datetime data type. "Signpost" puzzle from Tatham's collection. In the following table the first column shows a time string literal to be inserted into a database table column of the date or time data type shown in the second column. Often in our routines, especially when we create routine execution logs measuring the time taken to process, we have to store these records in a TIME field or a string that simulates the behavior of the TIME data type (Ex: 00: 01: 08.57800). Certainly, you shouldn't store the data that way. Has anyone been diagnosed with PTSD and been able to get a first class medical? You can use 19700101 for Unix epoch for example. Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Boolean algebra of the lattice of subspaces of a vector space? The following code shows the results of converting a time(4) value to a smalldatetime value. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. Extracting arguments from a list of function calls. What is this brick with a round back and a stud on the side used for? Why are players required to record the moves in World Championship Classical games? This format resembles the ISO 8601 definition for TIME excluding fractional seconds. http://sqlblog.com/blogs/uri_dimant/ Recently, I received a request inquiring how to convertMilliseconds to Days, Hours, Minutes, Seconds & Milliseconds. Two MacBook Pro with same model number (A1286) but different year, Short story about swapping bodies as a job; the person who hires the main character misuses his body, Copy the n-largest files from a certain directory to the current one. Asking for help, clarification, or responding to other answers. Heh lemme ask why you want to do this? What do hollow blue circles with a dot mean on the World Map? The best answers are voted up and rise to the top, Not the answer you're looking for? Azure Synapse Analytics SQL Trivia How to convert milliseconds to [hh:mm:ss.ms] format, SQL Error - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE, Python error: while converting Pandas Dataframe or Python List to Spark Dataframe (Can not merge type), Using IDENTITY function with SELECT statement in SQL Server, Reading JSON string with Nested array of elements | SQL Server 2016 - Part 3, SQL DBA - Windows could not start the SQL Server refer to service-specific error code 17051 - SQL Server Evaluation period has expired, SQL Error - SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs', Physical Join vs Logical Join in SQL Server, Informatica - There is no Integration Service found for this workflow (error). MS SQL Blog: Short story about swapping bodies as a job; the person who hires the main character misuses his body, Finding the distance from a corner of a cube to the midpoint of an edge. Find centralized, trusted content and collaborate around the technologies you use most. Which reverse polarity protection is better and why? Specifically i want to convert total_elapsed_time,last_elapsed_time,min_elapsed_time,max_elapsed_time these columns in seconds from sys.dm_exec_query_stats. Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. The TIME part of local DATETIME will be used. ashishashish This is fixed in 2014. Can corresponding author withdraw a paper after it has accepted without permission/acceptance of first author, Identify blue/translucent jelly-like animal on beach. Any fractional seconds precision value will cause the INSERT statement to fail. Content Discovery initiative April 13 update: Related questions using a Review our technical responses for the 2023 Developer Survey, How to return only the Date from a SQL Server DateTime datatype, How to check if a column exists in a SQL Server table, LEFT JOIN vs. LEFT OUTER JOIN in SQL Server. The DateAdd function is what you are looking for.. Use millisecond as the first parameter to the function, to tell it that you are adding milliseconds. Why did US v. Assange skip the court of appeal? The TSQL script mentioned in this article can be used to easily Convert Seconds to Minutes, Hours and Days. Reading Time: <1 minutes Hello people, Good afternoon! Thanks for contributing an answer to Stack Overflow! Hello guys, I have a column of integer data type that contains a millisecond data(for example 54013). Break it into two parts: seconds, then milliseconds. What does 'They're at four. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL). Ubuntu won't accept my choice of password, User without create permission can create a custom object from Managed package using Custom Rest API. Content Discovery initiative April 13 update: Related questions using a Review our technical responses for the 2023 Developer Survey, SQL: Convert duration in microseconds to DD:HH:MM:SS, Convert microsecond timestamp into day hour minute for google cluster traces in mysql, SQL Server : select column and do regex on it. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. 1 Day(s) 1 Hour(s) 20 Minute(s) 34 Second(s) 567 milliseconds(s), Your email address will not be published. (No one specified in question, and DATEPART is product specific), I know the OP's only asking about "time" durations, but for what it's worth, the pattern in @Lingo's solution scales better to durations >= 1 day than @t-clausen.dk's, because, How a top-ranked engineering school reimagined CS curriculum (Ep. One benefit of a view over a new column is that you don't have to change the base table schema (or worry about keeping it up to date). SELECT CONVERT(TIME,DATEADD (ms, @YOURMILLISECONDS, 0)), SELECT Seconds = ((@YOURMILLISECONDS% (1000*60*60)) % (1000*60)) / 1000, Best Regards,Uri Dimant SQL Server MVP, PowerAlerts The best SQL Server monitoring and alerting system you've ever seen! However, the conversion, although simple, is not so trivial and raises a lot of doubts, especially in novice SQL Server developers who end up even using functions to do this task and applying it to large volumes of data, impairing query performance. The following code shows the results of converting a time(4) value to a datetime value. Hopefully you are using stored procedures for data access so you aren't repeating this often. ), http://msdn.microsoft.com/en-us/library/system.datetime.ticks.aspx. When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value will be rounded up to fit. Even in SQL Server 2000, the server reported both duration and CPU time in . - Becker's Law 565), Improving the copy in the close modal and post notices - 2023 edition, New blog post from our CEO Prashanth: Community is the future of AI. Any time value will cause the INSERT statement to fail. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. In my SQL Server database, I have a datetime column. To summarize, we have a formula to extract and convert "Whole" days to "Whole" dates . Is it safe to publish research papers in cooperation with Russian academics? This section describes what occurs when a time data type is converted to other date and time data types. Is it for display purposes? Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. Dirceu, good afternoon! Visit Microsoft Q&A to post new questions. If you need to store the difference in a table, store it as a DateTime datatype that way, you can add them up later on and display the total either in a GUI or using one of the other conversion methods on this thread for the display. If you only want second precision you could divide the number by 1000 to get seconds, and by 86400 to get a fraction of a day, then add that to midnight on any nominal date - and convert the result to a string: select to_char (date '1970-01-01' + (21649000/86400000), 'HH24:MI:SS') as time from dual; TIME -------- 06:00:49 select to_char .
