Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2016
    Posts
    1

    Answered: get current day records from table using epoch

    Hi all,

    i have a query in which it records return as follows:

    -[ RECORD 1 ]-------------------+-------------------------------------
    id | 68780
    action | C
    actionepoch | 1428319646548
    authresult | SUCCESS
    billingagreement | TEST_NZ_DCB_2
    correlationid | 1331270836936081
    currency | NZD
    [ RECORD 2 ]-------------------+-------------------------------------

    and so on...
    I am trying to get the records done only today, so for example if today's date is 11012016, i will extract only those records. the actionepoch string contains the epoch time in which the records was done.However the actionepoch contains also the time and seconds, which are useless to me since i am focusing only on the current day and month.
    is there a way i can retrieve such records only by adding a match of the day/month in the query?

    Thanks in advance,
    Matthew

  2. Best Answer
    Posted by shammat

    "
    Code:
    select *
    from the_table
    where cast(to_timestamp(actionepoch / 1000) as date) = current_date
    More details in the manual: http://www.postgresql.org/docs/curre...ormatting.html"


  3. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    select *
    from the_table
    where cast(to_timestamp(actionepoch / 1000) as date) = current_date
    More details in the manual: http://www.postgresql.org/docs/curre...ormatting.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

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