|
Deib has asked for the wisdom of the Perl Monks concerning the following question:
Good evening monks!!
I've got a small question here.
I'm currently working on a application that checks out webpages for errors and such and uploads the info gathered to a database. Its almost done! But I'm just missing one thing. I can't get the datetime to be uploaded to the database. So, here's what I've got:
When I use this line: my $time = localtime(time); the resulting string is like this: Wed Mar 30 00:13:59 2005 . However, I need that info to look like this: 2005-03-30 00:13:59.
I know...this is REALLY simple stuff :S Now, the problem is that I am clueless as to how I can manage to work with this string bit by bit, reformatting every "word" to create a string understandable by MySQL.
First I would like to know if there is any module out there that can do this form me. I've checked in CPAN under "datetime" search, but I don't seem to find anything of interest. If there is none, I can do it myself right now, but for that, I would need someone to explain me first how to go through the string, word by word. |
Re: Converting localtime to SQL format
by jbrugger (Parson) on Mar 30, 2005 at 07:08 UTC
|
If you want to put the current time / date in a MySQL database, why don't you create a table with a fieldtype date/time and insert your query using now()?
eg:
# Datefield is of type: datetime
my $sth = $dbh->prepare('insert into tableName (DateField, OtherField)
+ values (now(),?)');
"We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.
| [reply]
[d/l] |
|
|
That was simple :S
Here I was reading hundreds of tutorials x_X
Thanks a lot jbrugger :)
| [reply] |
|
|
For the perpetually lazy, most databases have some way of defining default values. (although, with dynamic values such as this, you might have to use a trigger).
In mysql, you can just use the column type TIMESTAMP. It's been a while since I've played with oracle, but if setting the column 'DEFAULT SYSDATE' doesn't work. (It might've been 'DEFAULT SYSDATE()'), you can always use an insert trigger.
| [reply]
[d/l]
[select] |
Re: Converting localtime to SQL format
by brian_d_foy (Abbot) on Mar 30, 2005 at 07:35 UTC
|
| [reply] |
|
|
Thats interesting :)
thanks a lot
| [reply] |
Re: Converting localtime to SQL format
by blazar (Canon) on Mar 30, 2005 at 08:41 UTC
|
In addition to the good answers you already got, I think it is worth reminding that most perl functions return different values according to the context. In this case it wouldn't be too hard to roll your own(TM) solution:
my $time = do {
my ($s,$m,$h,$D,$M,$Y)=localtime;
$Y+=1900;
$M++;
"$Y-$M-$D $h:$m:$s" };
UPDATE: I added $M++ which was not not there as per crenz's warnings. | [reply]
[d/l]
[select] |
|
|
| [reply] |
|
|
You provide a good reason to rather use Posix::strftime(): Your solution is wrong since the month is zero-based and you forgot to increment the month.
Indeed. Mine was more of an example especially aimed at reminding of different contexts.
| [reply] |
Re: Converting localtime to SQL format
by jasonk (Parson) on Mar 31, 2005 at 15:15 UTC
|
Nobody has mentioned yet Time::Piece::MySQL, which makes localtime() return an object, so you can just do this:
use Time::Piece::MySQL;
print localtime->mysql_datetime."\n";
print localtime->mysql_date."\n";
print localtime->mysql_time."\n";
I generally prefer this to NOW() because I'm usually working with Class::DBI objects, rather than building my own queries, and you have to jump through hoops to get NOW() in there without getting quoted.
| We're not surrounded, we're in a target-rich environment! |
|---|
| [reply]
[d/l] |
|
|
