Re^2: Timestamp problem

[rashmi_k28]

This node falls below the community's threshold of quality. You may see it by logging in.

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