Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

Date calculations in Teradata

  • 28-02-2012 03: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

  • #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
Advertisement