Calculating Difference Between Two Timestamps In Oracle In Milliseconds


Answer :

When you subtract two variables of type TIMESTAMP, you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds.

  1  select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )   2*   from dual SQL> /  SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD') --------------------------------------------------------------------------- +000000000 14:51:04.339000000

You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND 

SQL> ed Wrote file afiedt.buf    1  select extract( day from diff ) days,   2         extract( hour from diff ) hours,   3         extract( minute from diff ) minutes,   4         extract( second from diff ) seconds   5    from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff   6*           from dual) SQL> /        DAYS      HOURS    MINUTES    SECONDS ---------- ---------- ---------- ----------          0         14         55     37.936

You can then convert each of those components into milliseconds and add them up

SQL> ed Wrote file afiedt.buf    1  select extract( day from diff )*24*60*60*1000 +   2         extract( hour from diff )*60*60*1000 +   3         extract( minute from diff )*60*1000 +   4         round(extract( second from diff )*1000) total_milliseconds   5    from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff   6*           from dual) SQL> /  TOTAL_MILLISECONDS ------------------           53831842

Normally, however, it is more useful to have either the INTERVAL DAY TO SECOND representation or to have separate columns for hours, minutes, seconds, etc. rather than computing the total number of milliseconds between two TIMESTAMP values.


Here's a stored proc to do it:

CREATE OR REPLACE function timestamp_diff(a timestamp, b timestamp) return number is  begin   return extract (day    from (a-b))*24*60*60 +          extract (hour   from (a-b))*60*60+          extract (minute from (a-b))*60+          extract (second from (a-b)); end; /

Up Vote if you also wanted to beat the crap out of the Oracle developer who negated to his job!

BECAUSE comparing timestamps for the first time should take everyone an hour or so...


Easier solution:

SELECT numtodsinterval(date1-date2,'day') time_difference from dates;

For timestamps:

SELECT (extract(DAY FROM time2-time1)*24*60*60)+  (extract(HOUR FROM time2-time1)*60*60)+ (extract(MINUTE FROM time2-time1)*60)+ extract(SECOND FROM time2-time1) into diff FROM dual;  RETURN diff;

Comments

Post a Comment

Popular posts from this blog

Converting A String To Int In Groovy

Answer : Use the toInteger() method to convert a String to an Integer , e.g. int value = "99".toInteger() An alternative, which avoids using a deprecated method (see below) is int value = "66" as Integer If you need to check whether the String can be converted before performing the conversion, use String number = "66" if (number.isInteger()) { int value = number as Integer } Deprecation Update In recent versions of Groovy one of the toInteger() methods has been deprecated. The following is taken from org.codehaus.groovy.runtime.StringGroovyMethods in Groovy 2.4.4 /** * Parse a CharSequence into an Integer * * @param self a CharSequence * @return an Integer * @since 1.8.2 */ public static Integer toInteger(CharSequence self) { return Integer.valueOf(self.toString().trim()); } /** * @deprecated Use the CharSequence version * @see #toInteger(CharSequence) */ @Deprecated public static Integer toInteger(String self) { return toInteg
Read more

"Cannot Create Cache Directory /home//.composer/cache/repo/https---packagist.org/, Or Directory Is Not Writable. Proceeding Without Cache"

Answer : if anyone pass through here, this is shorter solution: sudo chown -R $USER $HOME/.composer it seems to me the group information is missing in your command sudo chown -R <user> /home/<user>/.composer/cache/repo/https---packagist.org Shoud be sudo chown -R <user>:<group> /home/<user>/.composer/cache/repo/https---packagist.org But to avoid other permission issues, I would rather advise: sudo chown -R <user>:<group> /home/<user>/.composer/cache (you'll need access to other folders in there) and sudo chown <user>:<group> /home/<user>/.composer To make sure your user has permissions enough on the global composer folder. Mind the missing recursion so the user don't own keys created by root. If you need to find out the group: groups <user>
Read more

Android How Can I Convert A String To A Editable

Answer : As you probably found out, Editable is an interface so you cannot call new Editable() . However an Editable is nothing more than a String with Spannables, so use SpannableStringBuilder: Editable editable = new SpannableStringBuilder("Pass a string here"); If you only want to change the Spannables, never the text itself, you can use a basic SpannableString. Use Editable.Factory.getInstance().newEditable(str) From the android documentation: Returns a new SpannedStringBuilder from the specified CharSequence. You can override this to provide a different kind of Spanned.
Read more