|
Re: dbi and dates
by atcroft (Abbot) on Jul 09, 2002 at 04:26 UTC
|
MySQL (and other major databases, I suspect) have functions (such as UNIX_TIMESTAMP() in MySQL) which can return the time in Unix time format (for valid such times). Doing that, you can do something like "SELECT UNIX_TIMESTAMP(dt) FROM db;" to get the timestamp.... Some also include a form of DATATIME() function that you can specify the format, which may also help.
YMMV, but hope that helps lead you in the right direction....
| [reply] |
Re: dbi and dates
by abstracts (Hermit) on Jul 09, 2002 at 04:31 UTC
|
From Postgres docs http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/datatype-datetime.html
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional PostgreSQL, and others.
I don't know about Oracle, but any modern (or not so modern) database engine should provide either a standard time format, or has a function to convert to the time format you want.
Hope this helps,,, | [reply] |
Re: dbi and dates
by Abigail-II (Bishop) on Jul 09, 2002 at 08:46 UTC
|
What exactly do you mean by a "Perl date"? There's no such
thing as a "date" type in Perl. You might mean "seconds since
epoch", but even that is not a portable concept, as Perl will
use your OSses idea of an epoch. The epoch on a Unix system
will be 1-Jan-1970 00:00:00, but on VMS, it's 17-Nov-1858 00:00:00.
Also note that if you mean "seconds since 1970", there's no trivial
1-to-1 function to database specific dates, as they usually
have a much wider range and/or higher granuality.
Luckely, many databases will accept dates in wide range of
formats. If you stick to an ISO standard for instance, you
should have many problems.
Abigail | [reply] |
Re: DBI and dates
by hacker (Priest) on Jul 09, 2002 at 10:38 UTC
|
I've had no problem getting DATE, DATETIME,
and TIMESTAMP fields into and out of MySQL here (my database of choice at the moment). Let the database do the work for you. Here's one example:
$sth = $dbh->prepare
(qq{SELECT article_title, article_author,
DATE_FORMAT(article_date, '%W %M %D %Y') as my_date,
article_body from news_articles
where article_id = ?});
$sth->execute($article);
$sth->bind_col (1, \$article_title);
$sth->bind_col (2, \$article_author);
$sth->bind_col (3, \$article_date);
$sth->bind_col (4, \$article_body);
$sth->fetch;
article_date in this case, is a DATETIME field in MySQL, and presents itself with a date stamp similar to: 2001-09-22 17:09:20
The above code lets me present that date in a more human-readable format, as: Saturday September 22nd 2001
Does that help you a little? | [reply]
[d/l]
[select] |
Re: dbi and dates
by rdfield (Priest) on Jul 09, 2002 at 07:44 UTC
|
I use UnixDate and ParseDate (both in Date::Manip) to get the date and time formatted in Perl to whatever the NLS_DATE_FORMAT is set to in Oracle. The alternative to that would be have a TO_DATE in the SQL and explicitly coerce the date string.rdfield | [reply] |
Re: dbi and dates
by andye (Curate) on Jul 09, 2002 at 13:10 UTC
|
Is there a generic way to handle dates with DBI?
Unfortunately not (that I'm aware of, though there might be something on cpan). Each database has it's own routines. And even using the same database there can be configuration differences (e.g. in Oracle, whether to use 2-digit or 4-digit years by default).
For that reason it's worth pulling your date-handling stuff out into seperate routines - e.g. if I'm using MySQL then I'll knock up a quick sub to convert MySQL dates to epoch dates, and vice versa. Sometimes this means an extra SQL call, if you use the database itself to do the conversion - but I'm inclined to think it's worth this (tiny) efficiency hit because it makes the date-handling gubbins easier to replace if you change database.
Just my $.2
andy. | [reply] |
Re: dbi and dates
by boblawblah (Scribe) on Jan 07, 2008 at 01:35 UTC
|
| [reply] |