Re: Timestamp problem

[Tomte]

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...

[rashmi_k28]

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

[Tomte]

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.

[bart]

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.

[Anonymous Monk]

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))
        );