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
Assuming that by Unix time you mean
epoch
.UPDATE
Using
timestamptz
field in Postgres and an ISO datetime string from Javascript in order to properly deal with time zone.You can convert your "unix timestamp" to a date, then compare it with "today":
This assumes that your column named
"timestamp"
is not really atimestamp
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 propertimestamptz
or at leasttimestamp
data type).