1

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!

nickcoding2
  • 142
  • 1
  • 8
  • 34
  • The data you post looks like json, but the your query uses regular table syntax. One or the other is wrong. Please provide schema for stamp_table. If the query works in postgresql then we know it's a nodejs issue, and if the query doesn't work then you can eliminate the node.js part of the question. – Allan Wind Sep 12 '21 at 04:42
  • Apologies, I realized I format the rows as json objects before sending them back to client. Each row just has a stamp_id, timestamp, and title as the columns. Is that what you mean as schema? @AllanWind – nickcoding2 Sep 12 '21 at 04:45
  • In psql, `'\d stamp_table`, will give you the schema. No apology necessary, just update question. – Allan Wind Sep 12 '21 at 04:48
  • 1
    Why is your timestamp of type text? It will not compare the way you expect it to. – Allan Wind Sep 12 '21 at 04:49
  • @AllanWind What should the type be? I thought because I am concatenating all the date/time information in js to make a string object, I should be comparing against another string – nickcoding2 Sep 12 '21 at 04:51
  • You want to use the type `timestamp without time zone`, and use a non-reserved word for the column name. See https://www.postgresql.org/docs/current/datatype.html. – Allan Wind Sep 12 '21 at 04:57

1 Answers1

1

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

Allan Wind
  • 23,068
  • 5
  • 28
  • 38
  • Oh, I think I see--but then how would I modify the line that defines the 'hours' constant? And just to be super clear, why does this issue not plague rows that have values of say '2021-09-12 4:47:59' for the timestamp--is it because in that case the number of characters in the string matches? – nickcoding2 Sep 12 '21 at 04:57
  • 1
    The above answers your question, but the better way to fix it is to change the type of the timestamp column to `timestamp without time zone` then postgresql will do the right thing. If you want to do the padding on the nodejs side check out https://stackoverflow.com/questions/10073699/pad-a-number-with-leading-zeros-in-javascript – Allan Wind Sep 12 '21 at 04:58
  • So just to be 100% clear, because the documentation is a bit confusing as to what to actually type in for the ALTER TABLE command, am I changing type to 'timestamp' or 'timestampz'? – nickcoding2 Sep 12 '21 at 05:02
  • Ended up using this to change the type: https://stackoverflow.com/questions/43059108/postgresql-alter-column-data-type-to-timestamp-without-time-zone – nickcoding2 Sep 12 '21 at 05:11
  • 1
    timestamp (there is no timestampz; you were thinking of timestamptz). Cheers on sharing the solution. – Allan Wind Sep 12 '21 at 05:12