MYSQL Returning the timestamp after an insert statement

big_show

doing this in Java using JDBC using MySQL, my table just consists of an int column and a Timestamp column, when i insert an int, i want to return the Timestamp for that given int, I'd just use a select and where statement after the insert but some of the Int's can be the same so i can't uniquely identify them. I My SQL experience is noobish so please go easy if i'm missing something blindly obvious. many thanks.

Webmonkey

I'm not sure if it's possible to be honest - you need a unique field and you can obtain the last from mysql_insert_id when using an auto increment.

ron_darrell

In php I'd use the following command to get what you're looking for:

mysql_insert_id(connection);

I'm sure that the JDBC has a similar command. Time for google my friend

-RD

dazberry

You could do a select now(); to get the timestamp first and then use the resulting timestamp as part of your insert statement.

D.

amen

silly question but why do you want to do this?

if you are inserting and can't uniquely tell aftewards (multipe int values the same) why just return the current time and forgo the insert?

big_show

Amen, i'm going to use the Timestamp to identify the row, i can do that right?...

Webmonkey

big_show wrote: »

Amen, i'm going to use the Timestamp to identify the row, i can do that right?...

I'm a little confused. If you have the timestamp, why do you want to return the row if it's just what you inserted? :-/

amen

Amen, i'm going to use the Timestamp to identify the row, i can do that right?

you could but what are you doing with the time stamp and how are you going to use afterwards?

in english (ie code/computer ) what are you trying and how would you do it if it was a manually process?

TheWaterboy

Im assuming that your table has a unique key - return this instead of the timestamp to identify the row - mysql_insert_id()

More info here:

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Webmonkey

TheWaterboy wrote: »

Im assuming that your table has a unique key - return this instead of the timestamp to identify the row - mysql_insert_id()

More info here:

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

You assumed wrong unfort. We've mentioned that too but he doesn't have a unique key.

Tom Dunne

Can you do a post-insert trigger? Coming from an Oracle background, so no idea if MySQL can do that.

big_show

Thanks for the replies guys,

so going from my code below, i've taken on the advice, im looking to use the table to store bookings, but don't need worry about any details like name address etc, i pass the ticketnumber and timestamp (which im still figuring out! ) to the method. i'm just using the timestamp to identify the each ticket bulk so they can be later selected and deleted if need be.

public String bookTicket(String N, String Ts)
{
    /
    try {

          Statement statement = connection2.createStatement();
          statement.executeUpdate("insert into bookings (ticketnum, ts) values ('"+N+"', '"+Ts+"');");
          


        } catch(SQLException insertException) {
          System.out.println("booking catch");
        }

  String rt = "booked";
  return rt;
}

this is probably an arseways way of doing it, i'm still feeling my way around..cheers for the help guys!

BTW i'm returning the time-stamp to the user so they can use it as an id to "cancel" their tickets at a later stage if need be.
sorry if im not been clear

Webmonkey

Is the ticket number unique? - surely it is :-/ - if so that can be your index to uniquely identify rows.

big_show

i want to use the timestamp to identify each ticket grouping.

ie

ticketNum       timestamp
2                   4/7/09 21:36
2                   4/7/09 22:40
2                   4/7/09 22:44

so a person buys 2 tickets, database is updated with the booking, the user gets the timestamp, when they want to cancel they put in the timestamp and click cancel, entry is deleted.

see what i mean?

NeverSayDie

Well, an obvious problem there is that timestamps aren't unique, especially if they're only down to the minute like the ones you show there. If two users end up with tickets at 22:44, what happens when one of them tries to cancel? You definitely need to add a unique booking ID of some sort to your design.

Webmonkey

I agree that the design of your database is fundamentally wrong. I 'm still not even sure what you trying to achieve but then I'm tired today... :pac:

Tar.Aldarion

Would unix time do(perhaps along with number of tickets booked and maybe the destination) or do you think an indiv ticket id would be needed?

TheWaterboy

NeverSayDie wrote: »

Well, an obvious problem there is that timestamps aren't unique, especially if they're only down to the minute like the ones you show there. If two users end up with tickets at 22:44, what happens when one of them tries to cancel? You definitely need to add a unique booking ID of some sort to your design.

Exactly what I thought

I think OP you need to go back to the drawing board. This database design is going to cause nothing but problems. Create a new table with same fields and a unique id and just import your records into this.