Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2017
    Posts
    3

    Exclamation Unanswered: PostGreSQL unix timestamp value comparison

    Hi,

    How to compare between two dates in Postgresql having row value with unix timestamps.

    for example : table > ABC
    Column name > created_date -datatype bigint -
    Value -1395742273234 (unixtimestamps)

    I am getting actual datetime from unix timestamps with following syntax.
    Select to_char(to_timestamp(1395742273234 /1000),'yyyy-MM-dd HH:mm:ss');

    output : 2014-03-25 03:03:13

    I want to get results between two dates ( 2014-03-25 03:03:13 and 2014-03-30 03:55:13) but no results. Please guide.

    Thanks

  2. #2
    Join Date
    Aug 2017
    Posts
    13
    Provided Answers: 1
    You are trying to compare dates (get results between 3/25/2014 and 3/30/2014) but you are converting the values to a string (to_char). In order to compare them as dates they need to BE dates. You need to leave it at

    Select to_timestamp(1395742273234 /1000);

    or if you really need to convert it to the string for some reason you have to convert it back to do the comparison.

  3. #3
    Join Date
    Aug 2017
    Posts
    3
    I want to get results between date with timestamp. How it'll compare and return the valid output.
    for example : ( between 3/25/2014 03:55:10 and 3/30/2014 11:55:55).

    Thanks

  4. #4
    Join Date
    Aug 2017
    Posts
    13
    Provided Answers: 1
    Code:
    Select to_char(to_timestamp(1395742273234 /1000),'yyyy-MM-dd HH:mm:ss');
    where to_timestamp(1395742273234 /1000) 
    between '03/25/2014 03:05:10' and '03/30/2014 11:55:55'
    If you want to be explicit in the where statement about the form you can also
    Code:
    between to_timestamp('03/25/2014 03:05:10','MM/DD/YYYY HH24:MI:SS') and to_timestamp('03/30/2014 11:55:55','MM/DD/YYYY HH24:MI:SS')
    Last edited by indiri; 08-16-17 at 13:31.

  5. #5
    Join Date
    Aug 2017
    Posts
    3

    Thumbs up Syntax works

    It works for me. Appreciate your response. Thanks

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
  •