how can i get the all data from postgresql thats from the current day, between the hours of 00:00:00-CodePudding

very new to the backend as well as all things postgresql, at the moment all ive been able to do is

SELECT * FROM nutrition WHERE timestamp >= (extract(epoch from now())::bigint * 1000) - 86400000 AND timestamp <= (extract(epoch from now())::bigint * 1000)   86400000

in the frontend using js, im using Date.now() to store the timestamp in the DB.

timestamp is a column in my db thats logging the unix time in bigint format in which the food was logged. I want to get all the data from the current day from the hours beteen 12 AM midnight, and 11:59 PM. thanks.

for example, the last item i logged was last night at 10pm (1663995295337 UNIX time) so the data shouldnt include it.

show timezone returns;

America/Los_Angeles

Solution below --------------------------------------------------------------------

 const today = new Date();
  const beginningOfDay = today.setUTCHours(7, 0, 0, 0);
  const endOfDay = today.setUTCHours(30, 59, 59, 99);

  switch (method) {
    case "GET":
      try {
        const text = `
    select 
     * 
    from 
       nutrition 
    where 
      timestamp 
    between ${beginningOfDay} and ${endOfDay}`

this was the solution i was looking for, thanks for the help. sorry if i wasnt descriptive enough.

CodePudding user response：

Assuming that by Unix time you mean epoch.

select extract(epoch from now());
      extract      
-------------------
 1664038032.392004

 select to_timestamp(1664038032.392004);
          to_timestamp          
--------------------------------
 09/24/2022 09:47:12.392004 PDT


select 
 * 
from 
   some_table 
where 
   to_timestamp(1664038032.392004) 
between 
    current_date   '00:00:00'::time AND current_date   '23:59:59'::time

UPDATE

Using timestamptz field in Postgres and an ISO datetime string from Javascript in order to properly deal with time zone.

create table tsz_test(id integer, tsz_fld timestamptz);

--In Web frontend today = new Date().toISOString(); "2022-09-24T20:57:05.830Z"


insert into 
    tsz_test 
values (1, '2022-09-24T20:57:05.830Z'), (2, '2022-09-25T08:57:05.830Z');

select * from tsz_test ;
 id |          tsz_fld           
---- ----------------------------
  1 | 09/24/2022 13:57:05.83 PDT
  2 | 09/25/2022 01:57:05.83 PDT

--Borrowing from @a_horse_with_no_name answer
select * from tsz_test where tsz_fld::date = '09/24/2022'::date;
 id |          tsz_fld           
---- ----------------------------
  1 | 09/24/2022 13:57:05.83 PDT

CodePudding user response：

You can convert your "unix timestamp" to a date, then compare it with "today":

where to_timestamp("timestamp")::date = current_date

This assumes that your column named "timestamp" is not really a timestamp

If the column doesn't actually store seconds (which would be a unix epoch), but milliseconds you need to_timestamp("timestamp"/1000)::date instead (another source of problems that wouldn't exist if you had used a proper timestamptz or at least timestamp data type).