CAST in executeSQLQuery

Version 1
    This document was generated from CDN thread

    Created by: Rene Forstner on 31-10-2012 06:46:14 AM
    Hi all,
     
    We need to get some informations from the extensionmobilitydynamic table requesting via axl executeSQLQuery.
     
    Is there a way to cast the unix timestamp from the "DateTimeStamp" column to a "readable" datetime field, during the query?
     
    At the moment the query looks like this
    select enduser.userid, device.name from extensionmobilitydynamic,
    device, enduser where extensionmobilitydynamic.fkdevice = device.pkid and 
    extensionmobilitydynamic.fkenduser_lastlogin = enduser.pkid and
    extensionmobilitydynamic.fkenduser IS NULL
    now i need the lastLoggedOn users for the last week only.
     
    I've tried the following queries, but nothing would work:
     
    select cast(DateTimeStamp as DateTime)…

    <!--[if !supportLists]-->

    select …..where datetimestamp >= UNIX_TIMESTAMP(‘2012-10-20’)
    select date(DateTimeStamp)…
     
    CUCM Version: 8.6.1
     
    kind regards
    rené

    <!---->

    Subject: RE: CAST in executeSQLQuery
    Replied by: David Staudt on 31-10-2012 10:33:30 AM
    The UCM database use Informix IDS, reference herer:
     
     
    Some research led me to this, which seems to be a step in the right direction:
     
    select dbinfo('utc_to_datetime',datetimestamp) as dt from extensionmobilitydynamic
    -------------------
    2011-09-12 12:51:18.0
    2012-04-17 16:42:34.0
    2012-07-19 16:12:48.0
    2012-02-27 16:20:59.0
     

    Subject: RE: CAST in executeSQLQuery
    Replied by: Rene Forstner on 31-10-2012 10:37:56 AM
    Hi David,
     
    Thank you, thats exactly the query i'm looking for!