Search code examples
mysqldatecastingteradatateradata-sql-assistant

Conversion of Teradata sql to MYSQL sql

I want to convert Teradata query into MYSQL query.

Datatype of START_TIME AND END_TIME is TIMESTAMP(6)

Teradata query:-

select START_TIME,END_TIME, (EXTRACT(DAY    FROM (END_TIME - START_TIME DAY(4) TO SECOND)) * 86400) from base.xyz

Result is like:-

**START_TIME, END_TIME, CALCULATED_FIELD**

9/15/2017 16:22:52.000000   9/19/2017 15:14:02.000000   259,200
7/26/2014 07:00:04.000000   7/28/2014 12:55:55.000000   172,800
6/8/2018 16:59:19.000000    6/11/2018 09:56:23.000000   172,800
10/6/2017 17:52:06.000000   10/9/2017 15:47:35.000000   172,800
7/29/2014 02:40:00.000000   7/30/2014 04:09:56.000000   86,400
7/25/2014 08:33:35.000000   7/25/2014 09:43:34.000000   0
1/6/2015 13:56:52.000000    1/6/2015 13:57:29.000000    0

Please suggest what would be mysql query

Solution

  • Try

    SELECT start_time,
       end_time,
       TIMESTAMPDIFF(DAY, start_time, end_time) * 86400
FROM base.xyz;

    fiddle