#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    1
    Rep Power
    0

    Cannot format result of date query


    I have a query that shows the difference between the date of a service and when it is documented. Staff have a 3 day window for compliance. This query provides monitoring of it. In Sybase, it yielded a result of difference in days (negative if postdated, 0/1 for same day, and 2-? if note was written after the event took place).

    In postgresql, the database used date timestamp to record time. This is the result:

    date service date created(documented) difference
    "2008-08-15 13:45:38.306";"2008-08-15 13:48:18.426";"-00:02:40.12"
    "2008-08-15 13:45:38.306";"2008-08-15 13:50:20.043";"-00:04:41.737"
    "2008-08-15 18:42:18.076";"2008-08-20 11:26:25.85";"-4 days -16:44:07.774"
    "2008-08-15 18:42:18.076";"2008-08-20 11:26:32.5";"-4 days -16:44:14.424"
    "2008-08-15 22:53:57.606";"2008-08-15 23:59:18.133";"-01:05:20.527"
    "2008-08-15 22:53:57.606";"2008-08-16 00:00:29.796";"-01:06:32.19"

    I need the difference column to display whole days -4 / 1 / 2/ 3 / etc.

    Here is the query that I am using, and I just cannot figure out how to have the difference column to do what I want:

    select date_service, date_creation, (date_service -MIN (date_creation)) AS diff FROM notes
    GROUP BY date_service, date_creation;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    I'm not entirely sure that I understand your question (your query is way more complicated than mine...), but I think this is what you want:

    [code=SQL]SELECT * FROM sd;

    service | created
    ---------------------------+---------------------------
    2013-06-27 11:09:53.12812 | 2013-06-27 11:09:53.12812
    2008-08-15 13:45:38.306 | 2008-08-15 13:48:18.426
    2008-08-15 13:45:38.306 | 2008-08-15 13:50:20.043
    2008-08-15 18:42:18.076 | 2008-08-20 11:26:25.85
    2008-08-15 18:42:18.076 | 2008-08-20 11:26:32.5
    2008-08-15 22:53:57.606 | 2008-08-15 23:59:18.133
    2008-08-15 22:53:57.606 | 2008-08-16 00:00:29.796
    (7 rows)


    SELECT service, created, (service - created)::interval day AS difference
    FROM sd;

    service | created | difference
    ---------------------------+---------------------------+------------
    2013-06-27 11:09:53.12812 | 2013-06-27 11:09:53.12812 | 00:00:00
    2008-08-15 13:45:38.306 | 2008-08-15 13:48:18.426 | 00:00:00
    2008-08-15 13:45:38.306 | 2008-08-15 13:50:20.043 | 00:00:00
    2008-08-15 18:42:18.076 | 2008-08-20 11:26:25.85 | -4 days
    2008-08-15 18:42:18.076 | 2008-08-20 11:26:32.5 | -4 days
    2008-08-15 22:53:57.606 | 2008-08-15 23:59:18.133 | 00:00:00
    2008-08-15 22:53:57.606 | 2008-08-16 00:00:29.796 | 00:00:00
    (7 rows)[/code]
    If you need the zero columns to say an actual 0, you could make that happen with a cast through to_char():
    [code=SQL]SELECT service, created,
    to_char((service - created), 'DD')::integer AS difference
    FROM sd;

    service | created | difference
    ---------------------------+---------------------------+------------
    2013-06-27 11:09:53.12812 | 2013-06-27 11:09:53.12812 | 0
    2008-08-15 13:45:38.306 | 2008-08-15 13:48:18.426 | 0
    2008-08-15 13:45:38.306 | 2008-08-15 13:50:20.043 | 0
    2008-08-15 18:42:18.076 | 2008-08-20 11:26:25.85 | -4
    2008-08-15 18:42:18.076 | 2008-08-20 11:26:32.5 | -4
    2008-08-15 22:53:57.606 | 2008-08-15 23:59:18.133 | 0
    2008-08-15 22:53:57.606 | 2008-08-16 00:00:29.796 | 0
    (7 rows)[/code]
    Last edited by zxq9; June 26th, 2013 at 09:28 PM.

IMN logo majestic logo threadwatch logo seochat tools logo