Question

How to get CURRENT_TIMESTAMP for MS SQL Azure

  • 29 September 2020
  • 5 replies
  • 184 views

I’m looking to extract a simple CURRENT_TIMESTAMP as a field in my LookML. For a MS SQL Azure DB.


However, I keep getting the error: ’ The Microsoft Azure SQL Database database encountered an error while running this query.


Each GROUP BY expression must contain at least one column that is not an outer reference.’


My example LookML:


 dimension_group: current_time_test {
type: time
timeframes: [time, date, week, month]
sql: CURRENT_TIMESTAMP ;;

}


5 replies

Userlevel 5
Badge

Try adding


datatype: datetime


It might do the trick

Thanks Cyril,

I actually solved it in the end converting to a measure, not a dimension-group.


  measure: current_time {
type: date_time
sql: CURRENT_TIMESTAMP ;;

}


Thank you all the same though!

Userlevel 5
Badge

Hmm that seems odd though to use a measure for that. How do you intend to use that timestamp?

Apparently there is a known limitation of certain database dialects like SQL Server and MySQL that occurs when you try to group by a literal, with Looker. Looker automatically groups by any dimension included in the query so you’ll always run into this issue with SQL Server/MySQL in Looker.


http://www.sql-server-helper.com/error-messages/msg-164.aspx


So I am referencing the CURRENT_TIMESTAMP as a measure. For my period-on-period comparison range. Once I’ve got the complete solution I might post it here as it hasn’t been easy to resolve for SQL Azure.

Userlevel 5
Badge

I undestand.


You could also use the liquid {{ now }} to get the UTC timestamp

Reply