skip to Main Content

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.

2

Answers


  1. 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
    
    
    
    Login or Signup to reply.
  2. 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).

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search