TQL date and time functions

In oracle I can do things like this:

select 
    extract(hour   from MEASUREMENT_TIMESTAMP), 
    extract(minute from MEASUREMENT_TIMESTAMP), 
    extract(year   from MEASUREMENT_TimeSTAMP), 
    extract(month  from MEASUREMENT_TimeSTAMP), 
    extract(day    from MEASUREMENT_TimeSTAMP), 
    to_char(            MEASUREMENT_TimeSTAMP,'AM'),      
    to_char(            MEASUREMENT_TimeSTAMP,'DY'),     
    to_number(to_char(  MEASUREMENT_TIMESTAMP,'HH24MI')) 
from MY_MEASURES_FACT_TABLE;

 

however I do not see that functionality with TQL

https://docs.thoughtspot.com/5.2/reference/sql-cli-commands.html

All I see is this:

You can use the following date functions:

  • absyear
  • absmonth
  • absday
  • absquarter
  • date
  • time

 

can someone help me simulate how to extract hour, minute, year, month, AM/PM from date_time columns via TQL?

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • I was able to get PART of the answer by doing this

     

    TQL [database=indot]> select absyear(MEASUREMENT_TIMESTAMP) as YR, absmonth(MEASUREMENT_TIMESTAMP) as MON, absday(MEASUREMENT_TIMESTAMP) as DY  from DW_MOBILITY_FACT ;
    YR|MON|DY
    ---------
    2011|501|15263
    2011|501|15278
    2011|501|15278
    2011|501|15251
    2011|501|15270
     

    I understand the year above, but I do NOT understand the absday and absmonth result.

     

    Can someone help me ?

    Reply Like
  • from the front end, you can use hour_of_day to get the hour from a given date time column. 

    you can also use year or month. 

    I don't think there is a minute keyword. 

    Reply Like 1
Like Follow
  • Status Answered
  • 4 mths agoLast active
  • 2Replies
  • 19Views
  • 2 Following