Creating a prepare-bind insert statement with a TIMESTAMP datatype

Question

I am trying to insert some rows using a prepare-bind statement. One of the columns is of type TIMESTAMP. How do I bind the variable for this column with the current epoch time?

I have the following table:

CREATE TABLE orders {
  user_id TEXT,
  order_time TIMESTAMP,
  order_id UUID DEFAULT gen_random_uuid(),
  order_details TEXT,
  PRIMARY KEY (user_id, order_time DESC, order_id),
  FOREIGN KEY (user_id) REFERENCES %s (user_id)
);

I have the following prepare statement code:

String stmt = "INSERT INTO orders " +
              "(user_id, order_time, order_details) " + 
              "VALUES (?, ?, ?);"
PreparedStatement p = connection.prepareStatement(stmt);

Now I am trying to bind the current epoch time System.currentTimeMillis() to variable 2 in this statement. How should I do that?

can you clarify the last sentence please? – Youcef LAIDANI Sep 10 '19 at 05:45 — Youcef LAIDANI, Sep 10 '19 at 05:45

score 1 · Accepted Answer · answered Sep 10 '19 at 05:44

1

The corresponding Java type to your SQL's TIMESTAMP is most likely java.sql.Timestamp. You can generate a current timestamp using:

Timestamp timestamp = new Timestamp(System.currentTimeMillis());

So, your prepared statement code might look like:

String stmt = "INSERT INTO orders " +
              "(user_id, order_time, order_details) " + 
              "VALUES (?, ?, ?);"
PreparedStatement p = connection.prepareStatement(stmt);
p.setString(1, "Karthik");
p.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
p.setString(3, "here is some order text");

answered Sep 10 '19 at 05:44

Tim Biegeleisen

502,043
27
286
360

The OP said `to variable 3 in this` this make the question unclear, nice answer as well :) – Youcef LAIDANI Sep 10 '19 at 05:47
@YCF_L You didn't need to delete your answer, I was only pointing this out to you :-) – Tim Biegeleisen Sep 10 '19 at 05:48
Its ok, the question was not clear, I will wait the OP to clarify it, but your answer is more reasonable – Youcef LAIDANI Sep 10 '19 at 05:49
My bad. I did mean variable 2, have updated my question. This does solve my issue, thanks folks! – Karthik Ranganathan Sep 10 '19 at 17:32

Creating a prepare-bind insert statement with a TIMESTAMP datatype

1 Answers1