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

    Join Date
    Jul 2010
    Posts
    6
    Rep Power
    0

    How to extract microsecs from timestamp.


    In DB2 I was able to extract microsecs with the query

    "SELECT MICROSECOND(CRT_TMS) FROM TABLEA"

    So, for time stamp '2006/01/19 12:08:18.123456', the microsec will be 123456

    Similarly,
    I am trying to figure out the equivalent query for Postgres.

    Thanks,
    Om
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    I agree this is really hard to find in the manual.

    It's hidden behind the heading "Date/Time Functions and Operators", who would have guessed that....

    http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

    Code:
    SELECT extract(microseconds FROM CRT_TMS) 
    FROM TABLEA
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    6
    Rep Power
    0
    Thanks for the link

    Here is example I found there
    SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
    Result: 28500000

    Originally Posted by shammat
    I agree this is really hard to find in the manual.

    It's hidden behind the heading "Date/Time Functions and Operators", who would have guessed that....

    http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

    Code:
    SELECT extract(microseconds FROM CRT_TMS) 
    FROM TABLEA
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    You'll need to apply some maths on the result. It's probably easier to wrap that into a function

    Something like this:

    Code:
    create or replace function microseconds(timestamp)
    returns numeric
    as
    $$
      select ((extract(seconds from $1) - trunc(extract(seconds from $1))) * 1000000)::numeric;
    $$
    language sql
    immutable;

IMN logo majestic logo threadwatch logo seochat tools logo