Topic
  • 4 replies
  • Latest Post - ‏2012-09-09T22:15:18Z by mgibson
mgibson
mgibson
3 Posts

Pinned topic Convert UTC/GMT datetime to local...

‏2012-08-31T04:34:33Z |
Hi,

Does anyone know how to convert a UTC datetime to localtime within a SELECT statement?

I need to develop a report against an Informix database that stores its datetime values as UTC, and we wish to show Local datetime in a report instead (and filter on the local datetime).

Any ideas?

Regards
Mick
Updated on 2012-09-09T22:15:18Z at 2012-09-09T22:15:18Z by mgibson
  • SystemAdmin
    SystemAdmin
    1143 Posts

    Re: Convert UTC/GMT datetime to local...

    ‏2012-08-31T05:55:52Z  
    Hi Mick,
    depends on the IDS version you are using and the way the UTC time is stored in your table. Since 11.5 there is the 'utc_to_datetime' option of DBINFO function, which you can use to casts the numeric expression representing a Coordinated Universal Time (UTC) value to a DATETIME value in the time zone of the database server:

    SELECT DBINFO ('utc_to_datetime', timesheet.utc_checkin ) from <tablename> ....

    For details please have a look at
    http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids_sqs_1495.htm

    HTH, Tomas
  • mgibson
    mgibson
    3 Posts

    Re: Convert UTC/GMT datetime to local...

    ‏2012-09-03T00:14:44Z  
    Hi Mick,
    depends on the IDS version you are using and the way the UTC time is stored in your table. Since 11.5 there is the 'utc_to_datetime' option of DBINFO function, which you can use to casts the numeric expression representing a Coordinated Universal Time (UTC) value to a DATETIME value in the time zone of the database server:

    SELECT DBINFO ('utc_to_datetime', timesheet.utc_checkin ) from <tablename> ....

    For details please have a look at
    http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids_sqs_1495.htm

    HTH, Tomas
    Hi Tomas,

    I very much appreciate the reply - thanks. In reading the details in the link you attached, it seems as though that function's 2nd parameter only accepts an integer - not a datetime field. My data is stored in the database as a datetime - do you know how I can convert the date stored in the database to the specific type of integer required by the function?

    I'll continue to search for the answer myself, but thought I'd post this just in case I get a quicker response.

    Any further advice is greatly appreciated!

    Regards
    Mick
  • SystemAdmin
    SystemAdmin
    1143 Posts

    Re: Convert UTC/GMT datetime to local...

    ‏2012-09-07T13:49:50Z  
    • mgibson
    • ‏2012-09-03T00:14:44Z
    Hi Tomas,

    I very much appreciate the reply - thanks. In reading the details in the link you attached, it seems as though that function's 2nd parameter only accepts an integer - not a datetime field. My data is stored in the database as a datetime - do you know how I can convert the date stored in the database to the specific type of integer required by the function?

    I'll continue to search for the answer myself, but thought I'd post this just in case I get a quicker response.

    Any further advice is greatly appreciated!

    Regards
    Mick
    Hello Mick,
    unfortunately i haven't found any simple way of how to convert the datetime value stored in database to the UTC value. The problem here is that the server doesn't know what timezone the datetime value comes from, so it can't do the proper adjustments.

    So the only possible way of how to converts UTC datetime to localtime in your case is to write a user define function which will add/subtract the time difference between the UTC and you local timezone. Something like:

    create function utc2local (utc datetime year to second, diff integer)
    returning datetime year to second;

    return (utc + (diff) units minute);

    end function;

    If your local time is say UTC+10 hours (i.e. UTC+600 minutes), you can then call the function in a SELECT statement like (supposing the col3 holds the utc datetime value):

    select col1,...,utc2local(col3,600) ... from table;

    HTH, Tomas
  • mgibson
    mgibson
    3 Posts

    Re: Convert UTC/GMT datetime to local...

    ‏2012-09-09T22:15:18Z  
    Hello Mick,
    unfortunately i haven't found any simple way of how to convert the datetime value stored in database to the UTC value. The problem here is that the server doesn't know what timezone the datetime value comes from, so it can't do the proper adjustments.

    So the only possible way of how to converts UTC datetime to localtime in your case is to write a user define function which will add/subtract the time difference between the UTC and you local timezone. Something like:

    create function utc2local (utc datetime year to second, diff integer)
    returning datetime year to second;

    return (utc + (diff) units minute);

    end function;

    If your local time is say UTC+10 hours (i.e. UTC+600 minutes), you can then call the function in a SELECT statement like (supposing the col3 holds the utc datetime value):

    select col1,...,utc2local(col3,600) ... from table;

    HTH, Tomas
    Tomas,

    I won't go to that extent, the database is packages with a Cisco call centre system as a complete appliance, and we don't want to go messing with the DB. But thank you most sincerely for your help in any case.

    I've added 10 hours to the datetime in the reports, it just means that our reports will have to ignore daylight savings.

    Thanks again!

    Mick