Question 88967 in PostgreSQL Question posted in
The official documentation can be found here.

How to return all value that have same date and time in psql – Postgresql

I have created alarm that insert into database based on type of event at the same time.
I did query

select * 
from alrm 
where ble=$bleAddress and datetime=(select max(datetime) from alrm

to get the list of data from alrm table. Turned out, the query just returned one row which is the max tid of the datetime value.

I want it to return all value that have same ‘datetime’ in minute. I dont know how to filter out in minute and get the list of event that have same ‘datetime’ (up to minute)

Tags:

3

Answers


  1. 0

    Use Group By condition, for groupping all max datetime by address or other column, for example:

    SELECT address, MAX(datetime)
FROM alrm
GROUP BY address, datetime;

    You can change address to another column for groupping data

    Login or Signup to reply.
  2. 0

    You can use to_char() to return a formatted timestamp as text, in our case 'YYYY-MM-DD HH24:MI' return only the date, hours and minutes. No seconds.

    SELECT * 
FROM alrm
WHERE ble = $bleAddress AND to_char(datetime, 'YYYY-MM-DD HH24:MI') = (
        SELECT to_char(max(datetime), 'YYYY-MM-DD HH24:MI')
        FROM alrm
      );
    Login or Signup to reply.
  3. 0

    If I understand your question correctly you want to have returned all records within alrm, which have taken place for a specific address, within the same datetime (minutewise) as the latest entry in the table. If that is correct then this can be achieved by:

    SELECT 
    * 
FROM
    alrm 
WHERE 
    ble=$bleAddress and 
    DATE_TRUNC('minute', datetime::TIMESTAMP) = (SELECT DATE_TRUNC('minute', MAX(datetime)::TIMESTAMP) FROM alrm)

    Some things which immedatly catch my attention…. First of all, is it correct that you want to get all alarms for a specific address, which are equal in time (minutewise) to the latest alrm for ANY address? Don’t know the functional context, but it seems more logical if the limitation on ble=$bleAddress is also present in the sub query.

    Another point is the period to look back. Based on this specific functional requirement (all timestamps within the same minute) is very arbitrary to the moment the latest alarm took place. If it took place at 19:12:05 it will look back 5 seconds, and if it took place at 19:12:58 it will look back 58 seconds. Therefore better to look back exactly 1 minute, to have a window of time which is always the same. For example by doing:

    SELECT 
    * 
FROM
    alrm 
WHERE 
    ble=$bleAddress and 
    datetime::TIMESTAMP >= (SELECT MAX(datetime)::TIMESTAMP - interval '1 minute' FROM alrm)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search