MySQL TIMEDIFF(timestamp,timestamp) [ o/p: time ] function in HP Vertica ??

Hi Uday,

For your first question, did you try just subtracting the two timestamp columns?:

dbadmin=> create table t (col1 timestamp, col2 timestamp);
CREATE TABLE
dbadmin=> copy t from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2011-03-30 14:41:50|2011-03-29 22:35:21
>> 2011-03-30 14:42:08|2011-03-29 22:35:21
>> 2011-03-30 14:41:50|2011-03-29 23:46:22
>> \.
dbadmin=> select col1, col2, col1 - col2 from t;
        col1         |        col2         | ?column?
---------------------+---------------------+----------
 2011-03-30 14:41:50 | 2011-03-29 22:35:21 | 16:06:29
 2011-03-30 14:42:08 | 2011-03-29 22:35:21 | 16:06:47
 2011-03-30 14:41:50 | 2011-03-29 23:46:22 | 14:55:28
(3 rows)

For your second question, you're correct that there's no built-in function that provides this functionality.  (This would be a great opening for a third-party addition via our C++ SDK, if someone wanted to add it.)  However, the following should work:

dbadmin=> select to_timestamp(avg(extract(epoch from col1))) from t;
    to_timestamp    
---------------------
 2011-03-30 14:41:56
(1 row)

For "sec_to_time()" and "time_to_sec()":  So, Vertica works a little differently than MySQL.  In MySQL, the "time" data type can mean many things.  It can mean a time of day; it can mean the amount of time between two things; etc.

In Vertica, a time is actually a time of day.  If you have a time field that contains '9:00', that literally means 9:00am according to your clock.

So, say you subtract 11:00am and 2:00am.  That doesn't mean 9:00am; it means "they are 9 hours apart".  In Vertica, these are two different things.  9:00am is a column with the datatype "TIME".  '9 hours' is a column with the datatype "INTERVAL".  In general, if you take the difference of time types, you get an interval back; and you can't add time columns, you can add an interval to a time to get a new time.  (The INTERVAL type is smart about things like daylight savings time, etc., where "1 day" and "24 hours" are not equivalent.)

So -- do you want seconds for a TIME column, or seconds for an INTERVAL column?

The equivalent of "time_to_sec()" is actually the same for both -- the "EXTRACT()" function:
https://my.vertica.com/docs/6.0.x/HTML/index.htm#3669.htm
 

dbadmin=> select extract(epoch from col1) from t;
     date_part    
-------------------
 1301510510.000000
 1301510528.000000
 1301510510.000000
(3 rows)

"extract(epoch from [column])" on a TIME/TIMESTAMP/etc column returns the corresponding UNIX epoch, defined as the number of seconds since midnight on 1970-01-01.  (This may not be what you want, in which case, try extracting something other than "epoch".  See the doc link above.)  For an INTERVAL column, it just converts the interval to seconds and returns the result.

Note that Vertica's time and interval types have microsecond-level precision, so if you ask for a result in seconds, it may not be an integer.


For "sec_to_time()" -- on an INTERVAL, you just create one:

dbadmin=> select '3 seconds'::interval;
 ?column?
----------
 00:00:03
(1 row)

For a TIME, the question is "seconds after what?"

If your seconds are UNIX-epoch seconds (ie., after 1970-1-1 0:00:00), there's the TO_TIMESTAMP() function used in the example above:
https://my.vertica.com/docs/6.0.x/HTML/index.htm#9146.htm

If your seconds are seconds since the start of the day, then create the time 0:00:00 and add seconds to it:

dbadmin=> select '0:00:00'::time + '3 seconds'::interval;
 ?column?
----------
 00:00:03
(1 row)

It is a little more complicated than how MySQL works.  But we're just requiring that you be explicit about what you mean.  The goal is to help resolve/avoid weird bugs surrounding things like daylight-savings time, leap seconds, time zones, etc., where time doesn't necessarily work as nicely as you might like it to.

Adam

Hi Adam,

Thank you for your reply.

1.For my 1st Question ... table is already exist in database ... col1 and col2 are there.
I want to calculate the time difference between col1 and col2

               col1                        col2                                col1 - col2
2014-03-21 14:44:08    2014-03-20 19:39:52     0 19:04:16.000000
2014-03-21 14:44:08    2014-03-20 20:27:06     0 18:17:02.000000
2014-03-21 15:26:35    2014-03-20 21:12:28     0 18:14:07.000000

I don't want  0 19:04:16.000000   I want 19:04:16
--->
I tried this one 
>select “TIME”(col1 - col2) from t   -- it is working fine

 I replace this in Mysql code and i'm getting ... not able to find where i'm getting error

Error “TIME”(interval(in seconds)) does not exist,


2.select extract(epoch from col1) from t;

I don't want decimal part. could you round the output ..using any function.
ex: 1301510510.000000     ===> plz round this output to 1301510510 format


New 3) my table t  has a column col [data type time) values are like   "00:01:20"     ==>    HH:MM:SS

00:01:20  could you plz convert my col value to 120
00:15:11    could you plz convert my col value to 1511   using SELECT query  command 

Thank you in Advance

Hi Uday,

"table already exists" -- yes; I'm just demonstrating how I created the table that I tested with on my Vertica installation, so that if you're seeing a different result, you can try doing exactly the same thing that I did.  If you do exactly the same thing as I did and you get different results, then you must have an issue in your configuration or environment.  If you do exactly the same thing as I did and you get the same results as me, but you don't get those results when you do something similar on your table, then you should figure out how your table is different from mine, and either modify your table until it does what you want, or explain the difference here so that people on the forum can update these answers.

Back to your question:  When you run a SELECT statement on a date/time/interval/etc column, what you get back is not a string; it is a date or a time, in some internal binary format.  (This is true with Vertica, MySQL, etc.)

Some drivers/clients offer you the option to display that raw time value as a string.  It is, more or less, up to the client how to format that string.

My example output is copy/paste'd from Vertica's "vsql" command-line client.  As you can see, my output looks like what you are asking for (in your question 1).  So I'm assuming that you are not using vsql; that you are using a third-party client of some sort.  If that's the case, what client program are you using?  You will probably want to refer to that program's documentation on the INTERVAL data type; figure out how to configure its INTERVAL formatter.

If you want Vertica to always emit strings that look the same (and you can't or don't want to handle this at the client-program level), then what you want is not a TIME/etc column.  TIME is not a string; it's a time, there are many ways to write a time that all mean the same time.  What you need for that use case is a CHAR (or VARCHAR, etc).  In Vertica, TO_CHAR(), when used with the right format string, can make times look however you want:

https://my.vertica.com/docs/6.0.x/HTML/index.htm#9144.htm
https://my.vertica.com/docs/6.0.x/HTML/index.htm#9148.htm

I believe that TO_CHAR() can be used to answer your questions 1 and 3.


For your question 2, that one I'm going to give back to you.  What you're asking for isn't "rounding":  Rounding still returns a decimal value, it just happens to have all the decimals equal to 0 if you're rounding to the nearest whole number.  What you're asking for is how to cast a decimal (float or numeric) value to an integer.  Say you were in MySQL, and you had a numeric or floating-point value.  How would you turn it into an integer?  Vertica supports basic integer casts the same as MySQL. 


In general, if you're migrating between databases, you need to do your homework.  All databases, and all clients that access those databases, work slightly differently.  MySQL in particular is known for differing more than others from the SQL standard; its initial design was trying to be a very lightweight database that could respond to short, simple queries extremely quickly, which led them to choose simpler (but less powerful) representations for various operations and data types.  (Vertica is the opposite; not super-fast for simple queries on small data, but extremely fast and capable of much-more-sophisticated analytics on huge data sets.)  If you only have experience with MySQL, you may have a bit of a learning curve to learn Vertica.  So, you should go read Vertica's documentation; spend some time learning the new tool.  In particular, you could have answered most of your specific questions by reading through Vertica's documentation on all of its provided functions:

https://my.vertica.com/docs/6.0.x/HTML/index.htm#8941.htm

You could also try doing generic Web searches for your questions.  Vertica isn't identical to any one other database, but it is relatively similar to many other databases (though not so much MySQL).  PostgreSQL is an open-source DB with some good examples; there are various Vertica forums (and forums for other enterprise databases) that have examples as well.

You might also be interested in our online training:

http://www.vertica.com/customer-experience/12573-2/

It even has a course that covers timeseries analytics, which gets into much-more-sophisticated things to do with dates, times, timestamps, etc.

Adam