Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Date calculations in Teradata

Options
  • 28-02-2012 3:27pm
    #1
    Jagera
    Closed Accounts Posts: 910 ✭✭✭


    This is such a simple question, but I'm finding myself going in roundabouts.

    I want to get the difference, in seconds, between two dates which are stored as TIMESTAMP(6)

    I'm struggling to find good documentation or a decent online community for Teradata - so here I am...

    I have two working queries, neither returning exactly what I want, but it's as close as I can get: 
    (CAST ( (L.ENDDATE (FORMAT 'YYYYMMDDHHMISS') (VARCHAR(14))) AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS') - CAST ( (L.STARTDATE (FORMAT 'YYYYMMDDHHMISS') (VARCHAR(14))) AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS')) hour(4)  TO second(0
    which returns the time formatted as hh:mm:ss. It's good for reading the results but can't be used when calculating averages or pivoting the data.

    and
    (CAST ( (L.ENDDATE (FORMAT 'YYYYMMDDHHMISS') (VARCHAR(14))) AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS') - CAST ( (L.STARTDATE (FORMAT 'YYYYMMDDHHMISS') (VARCHAR(14))) AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS')) minute(4)
    which returns the difference in minutes - but not accurately. It just subtracts the minutes, i.e. 28/02/2012 12:35:59 - 28/02/2012 12:36:00 = 1 minute..

    I want the difference in seconds - so I can be more accurate for reporting.


Comments

  • Options
    #2
    Jagera
    Closed Accounts Posts: 910 ✭✭✭Jagera


    Got this sorted the other day, the answer is not like Oracle or SQL Server - so am posting it here in case anyone else comes across it..

    Teradata use something called a Day-Time interval - DAY, HOUR, MINUTE, SECOND - all fairly standard fine and return an INTEGER, but also included are DAY TO HOUR, DAY TO MINUTE, HOUR TO MINUTE - slightly different and these return a TIME data type.

    Furthermore, these intervals have a precision. so SECOND(2) holds a number up to 99, SECOND(4) up to 9999 - there's no bigger option - so calculating a number of seconds between 2 times - where the time is > 9999 means using the HOUR TO MINUTE option.

    Further-furthermore, the HOUR TO MINUTE option results in a TIME type. So 15500 seconds returns 04:18:30 (in HH:MM:SS)

    I wanted the number of seconds, so you need to use the EXTRACT function on, to get HOURS*3600 + MIN*60 + SECONDS

    Really painful.. Maybe there's a better way but I couldn't find it.


Advertisement