Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re: Timestamp problem

by Tomte (Priest)
on May 16, 2007 at 08:07 UTC ( #615709=note: print w/ replies, xml ) Need Help??


in reply to Timestamp problem

First things first:
Please ask your questions in english...this forum is an international effort, writing correct english as best as you can is not only polite but increases the chance of a helpful answer significantly - errors are normal and anticipated, but constructs as "CAn u plz" are frowned upon.

On to your problem:

  • Use placeholders, don't concatenate SQL statements and values
  • Use mysql onboard functions everywhere you can, there is an inverse function to the "unix_timestamp()" you are using, and thats "from_unixtime()"
Untested example to get you started:
[...] my $stmt = $dbh->prepare("insert into test1 values(FROM_UNIXTIME(?))") + or die("couldn't prepare insert: " . $@); $stmt->execute(time()-$old_time) or die ("couldn't perform insert: " . + $@); [...]
Consult The documentation available to repair my errors...

regards,
tomte


An intellectual is someone whose mind watches itself.
-- Albert Camus


Comment on Re: Timestamp problem
Download Code
Re^2: Timestamp problem
by rashmi_k28 on May 16, 2007 at 08:52 UTC
    I am not able to insert the exact format into mysql table. In my code I am subracting the old timestamp value with the current timestamp value. I am inserting the difference into the table but the inserted value is "0000-00-00 00:00:00" format. The value from_unixtime(1132344) should be converted into date format. If i use localtime(1132344) the value is not getting inserted. The data type used in the table is timestamp and not getting inserted into the table. How to use date format in perl as it can be used in php

      It gets inserted as "000..." because you pass an invalid date/timsetamp value - don't use localtime in scalar context, but in list-context and use the resulting array to assemble a string as described in the MySQL documentation.
      But as I tried to say: You don't need to format it, as you should be able to use FROM_UNIXTIME to convert a unix-timestamp (seconds since epoch) into something mysql is able to treat as an sql-timestamp.

      What I don't understand is, what you want to achieve, as you don't get a date by subtracting old_date from now(), that could be used as a timestamp in a meaningful way, but an intervall in seconds. You shouldn't store these as timestamp-values but as integers - as they aren't timestamps (referring to a point in time).

      In short: I guess you try to solve the wrong problem, or you didn't state the real problem - or maybe both..., nevertheless reading the docs I pointed you to should enable you to format the values you have to solve your stated problem in the way you want - namely inserting the difference as sql-timestamp.

      regards,
      tomte


      An intellectual is someone whose mind watches itself.
      -- Albert Camus

      The value from_unixtime(1132344) should be converted into date format. If i use localtime(1132344) the value is not getting inserted
      You're almost there. Use strftime from POSIX (a module that comes with perl) to convert the (list) output from localtime (or gmtime if your database is set to GMT/UT) into a string format MySQL recognizes. It should then be able to insert the dates into the database perfectly.
        here's what you likely ended up with :
        use POSIX; $dbh->do( 'INSERT INTO announcements ( msg, start_timestamp, end_timesta +mp ) VALUES (?,?,?)', undef, 'Hello World', strftime("%Y-%m-%d %H:%M:%S",localtime(time)), strftime("%Y-%m-%d %H:%M:%S",localtime(time+2000)) );

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://615709]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (7)
As of 2014-12-18 02:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (41 votes), past polls