Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Epoch date functions using INFORMIX 10

    HELP!

    I am having a hard time understanding how to use or convert epoch time into database dates or manipulation thereof. I have records that store the date in integer format. I need to be able to group by the "date" of the epoch number and not include the time. Here is an example of a sql statement.
    When I run this query I get the grouping down to the seconds. A possible 86400 results could return per user (one for each second of the day).

    How can I group by date only?

    $epoch_st_date=1338958800;
    $epoch_ed_date=1339477200;

    SELECT
    TRIM(DECODE(usr[1,3],'OpC','OPC','USER')),
    create_date
    FROM
    t1
    WHERE
    create_date >= $epoch_st_date
    AND
    create_date < $epoch_ed_date
    group by 1, 2
    order by 1, 2

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Did you try to play combining TRIM and the DBINFO('utc_to_datetime',epoch value) function ?

    something like
    SELECT TRIM(DBINFO("utc_to_datetime", -2134567890.91234) [9,2]
    FROM 'sysmaster:"informix".sysdual';

    I do not garantee "no syntax error" at frist shot, but this is an idea

    E.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •