Essentially, I have an a table in a database that is:
Each row has a stamp_id, timestamp, and title column. All columns are of type 'Text', Collation and Nullable are not defined, storage is 'extended' for all. In this particular row,
stamp_id: 'scenic',
timestamp: '2021-09-12 16:25:35',
title: 'yeesh'
and the 'current timestamp' is created by doing this:
let currentDate = new Date()
let date = ("0" + currentDate.getDate()).slice(-2);
let month = ("0" + (currentDate.getMonth() + 1)).slice(-2);
let year = currentDate.getFullYear();
let hours = currentDate.getHours();
let minutes = currentDate.getMinutes();
let seconds = currentDate.getSeconds();
let currentTimestamp = year + "-" + month + "-" + date + " " + hours + ":" + minutes + ":" + seconds
//(For reference, currentTimestamp evaluated to '2021-09-12 4:26:16' when I ran this code)
Then, I try to fetch all the objects in a database where the object timestamp is not 'expired' (I consider expired as having a timestamp value after the currentTimestamp.
const client = await pool.connect() //got a pool connection set up
let sqlStatement = 'SELECT * FROM stamp_table WHERE timestamp > ($1)'
let results = await client.query(sqlStatement, [currentTimestamp]);
The problem is, the above particular object is not being returned, although the object timestamp is clearly greater than currentTimestamp. I'm thinking it's something to do with the fact that the object has a timestamp with the hours passed the noon mark? No idea though honestly, any help would be much appreciated!
Your schema defines the timestamp column as type text. The timestamp text you pass in from nodejs use a different format, in particular, your example shows that hours is not zero padded. This means your timestamps string (hours) do not compare the way you expect:
select '16' < '4';
?column?
----------
t
(1 row)
The best way to fix this is to change the data type of your timestamp column to the type timestamp without time zone. PostgreSQL will then normalize it for you. You found a suitable process for changing the data type postgreSQL alter column data type to timestamp without time zone