when is a date not a date ?
Andy C
posts.pubdate column (et al) to an unsigned integer bizarre in the
extreme.
In modern databases, a date is normally represented a date which makes
sense. For example, this change now makes using SQL to select posts
difficult.
I don't doubt PHP has built in functions to convert Unix epoch numbers
to dates and times in lots of formats.
Don't get me wrong - I am now on the latest SVN and like the timezone
support (and scheduled posts) but out of idle curiosity, what was the
rationale for this ?
Andrew da Silva
Only problem with using Unix epoch is year 2038 when it will end...
Andy C
being represented as a number.
Chris Meller
Andrew da Silva
integer.
Main to reasons I can think of:
- DateTime object needs an strtotime() compatible string.
- Unix epoch makes mathematical manipulation easier and trustable?
- Less code/processing than asking PHP to convert your current date/
time in UTC.
Of course, this was done in a branch at first, to be reviewed.
If people bring forth counter-arguments, no grudge! ;)
Blake Johnson
> compatibility. It's easier to handle converting to and from INT and date on
> the DB side across platforms than handling timezones across platforms.
engines store dates. If we simply store integers, then we don't need
to worry about DBs applying their own timezone rules which might be
different than the PHP DateTime rules.
> For example, this change now makes using SQL to select posts
> difficult.
depend on DB vendor-specific syntax. Want all posts from a week ago?
Do something like:
$date = HabariDateTime::date_create( '1 week ago' );
$posts = DB::get_results( 'SELECT * FROM {posts} WHERE pubdate < ?',
array( $date->sql ) );
Or, simply use the built in functions:
$posts = Posts::get( array( 'before' => '1 week ago' ) );
--Blake
Andy C
I appreciate that from a developer viewpoint, for a plethora of
different reasons, it may well make the implementation (and your
lives) much easier.
Ironically enough, the use of HabariDateTime::date function actually
masks the physical implementation of this object in the schema.
This encapsulation should have helped to implement postdate as a UTC
date and adjust for the configured timezone.
However, from a data modeling viewpoint, implementing a date as a 32-
bit number is horrible. In fact, for a so-called next generation piece
of software, it's embarassing.
You remind me of the Oracle developer who maintained that using a
VARCHAR for a timestamp field was 'no problem'. He could easily tell
me the month, the day, the year, the minute from any given date - in
fact, he could tell me everything.
Well - no problem until I asked him to write me a 'tomorrow()'
function :-)
PS. If I am a mere user using PhpAdmin to access my Habari tables, am
I able to use PHP functions ?
Owen Winkler
>
> This encapsulation should have helped to implement postdate as a UTC
> date and adjust for the configured timezone.
Perhaps.
The current implementation doesn't have to deal with PDO returning
text-based values for dates. Everything starts in an integer format
that can be worked with. It shouldn't be incumbent upon the datetime
class to account for idiosyncrasies between date storage and output of
each database engine, that should be a function of the data layer.
> However, from a data modeling viewpoint, implementing a date as a 32-
> bit number is horrible. In fact, for a so-called next generation piece
> of software, it's embarassing.
It makes me sad that this email is so critical now. The schema has been
on the list for months, and the branch has contained this code for weeks
before the recent merge.
To me, it's more important that the software functions efficiently than
it allows native engine function manipulation of date types. Still, if
there's a way to have database engines return a usable format - one that
isn't returned as a string we have to parse - and allows us to store the
data in a native date format, it may yet be useful to explore it.
Owen
Mitch Pirtle
>
> To me, it's more important that the software functions efficiently than
> it allows native engine function manipulation of date types. Still, if
> there's a way to have database engines return a usable format - one that
> isn't returned as a string we have to parse - and allows us to store the
> data in a native date format, it may yet be useful to explore it.
I like the idea of storing timestamp ints for all dates. It does look
like the best bet for supporting different database platforms, and
actually makes search/retrieval faster while pushing more computation
out to the webserver.
I just launched a high-traffic site for a huge media company here in
Times Square that gets ~40 million page views weekly. I fretted the
entire time about application server performance, especially as it
used a totally object-driven MVC framework. The thing launched and
surprisingly enough, immediately became database bound,
throughput-wise.
It is a LOT easier to throw in more webservers, than it is to throw in
more database servers. Keeping the job on the database simple is a
really smart approach.
-- Mitch
Chris Meller
It is a LOT easier to throw in more webservers, than it is to throw in
more database servers. Keeping the job on the database simple is a
really smart approach.
Michael C. Harris
>
> However, from a data modeling viewpoint, implementing a date as a 32-
> bit number is horrible. In fact, for a so-called next generation piece
> of software, it's embarassing.
I know nothing of data modelling. Can you explain in words of two
syllables or fewer why this is so bad ? And explain what you think is
the right approach ?
--
Michael C. Harris, School of CS&IT, RMIT University
http://twofishcreative.com/michael/blog
IRC: michaeltwofish #habari
Brendan Borlase
>
> I like the idea of storing timestamp ints for all dates. It does look
> like the best bet for supporting different database platforms, and
> actually makes search/retrieval faster while pushing more computation
> out to the webserver.
The DB should be considered a raw data store-house. I'm not some leet
developer, or a massively programatically knowledgeable sod, so let me
put it in the terms that may best illustrate my humble point:
Consider a refrigerator - it keeps things cold and holds them in place. It
stores food in reasonably defined but flexible places.
You don't typically use a fridge to heat your food, or mix it, or fold it into
pretty shapes and serve it out for you. That takes other processes and
tools. When you keep a fridge, a fridge, it means any chef can use
anything else with it.
It remains highly compatible with pretty much any other kitchen tool or
appliance- and, most importantly, remains highly efficient.
When you start building many things into the fridge, or make up rules
about what can go in the fridge, how the fridge should store it, etc - the
bottleneck and feature set becomes all about _the fridge_. Not the chef.
Or his recipes. Or even the kitchen.
If the fridge then starts to become overloaded with tasks, it will eventually
start to fail in doing what it does best. Acting as simple, reliable storage.
My point?
The DB should be just a store. When you start using the DB engine for
computational work, rather than outright in/ out processing, it will slow
the hell down.
Just because you can query every record with * over a 5 million row table,
doesn't mean you should. Sometimes, less is more. Less is also much
much faster. And as we all know - the single worst thing is a slow
DB engine.
Everything _else_ falls apart as a result. The DB is key. Thus the fridge
is important.
So keeping data like dates as generic as well as cross-db-independant
as possible is going to mean Habari doesn't fall down the same endless
pit Wordpress has.
This is a long post. My appologies on that, but Habari is at the point
now where it can have all manner of 'features' that will absolutely KILL
DB performance.
It's critical that, where possible, data (outside of post content, perhaps)
is stored in as simple, un-processed state as possible.
Most web server engines have more than enough features and grunt,
along with PHP, to generate all sorts of content. Keep the load where
it can be scaled and leave the DB to be the fridge. :)
[Is this where I get kicked out for too-many-words illness? :)]
Cheers,
Brendan.
Michael C. Harris
[cleans the fridge]
> [Is this where I get kicked out for too-many-words illness? :)]
No, stop worrying.
Andy C
night monitoring svn commits. Indeed I am not qualified to do so.
I just thought the design merited some discussion and I am glad I
aired the topic.
For the record, I would have favoured a UTC timestamp with a wrapper
function to adjust for the local timezone.
Unsigned integers work. The functionality works (and more). It doesn't
matter to the end user or most developers. It ain't worth reverting.
There are some arguments for unsigned integers and it is clear a vote
would have confirmed the current decision.
This snippet from the wonderful (and now broken) monthly archives
plugin illustrates the point beautifully
$q= 'SELECT YEAR( p.pubdate ) AS year, MONTH( p.pubdate ) AS month,
COUNT( p.id ) AS cnt
FROM ' . DB::table( 'posts' ) . ' p
WHERE p.content_type = ? AND p.status = ?
GROUP BY year, month
ORDER BY p.pubdate DESC ' . $limit
Self-documenting, captures all the semantics in five lines, portable
to all database platforms.
Now, obviously, this can be fixed by coding the equivalent in PHP -
it's only 1's and 0's after all but I doubt the solution would be as
concise and elegant.
Oh and please don't tell me integers are better for performance. I
work for Oracle. Databases are here to stay :-)
Habari - the next generation blogging platform (at least until 2038)
Stanley FONG
rick c
not MySQL or Postgres. It should no longer be broken. If it is, please
reopen the ticket for it. Also note that no php was required in the
query.
As a side note, functions like MONTH, YEAR, UNIX_TIMESTAMP (which is
all that is needed to get the query to work), NOW, etc are *not*
portable to all database platforms. The minute you get beyond the very
basics, translations have to be made.
Rick
rick c
Rick
Blake Johnson
> This snippet from the wonderful (and now broken) monthly archives
> plugin illustrates the point beautifully
>
> $q= 'SELECT YEAR( p.pubdate ) AS year, MONTH( p.pubdate ) AS month,
> COUNT( p.id ) AS cnt
> FROM ' . DB::table( 'posts' ) . ' p
> WHERE p.content_type = ? AND p.status = ?
> GROUP BY year, month
> ORDER BY p.pubdate DESC ' . $limit
>
> Self-documenting, captures all the semantics in five lines, portable
> to all database platforms.
>
funcitons. So, this query is not portable to all platforms. It
happened to work, previously, because we provided translations for
SQLite and PostgreSQL.
I will also mention that you don't have to write your own query to do
this. It is provided by the API with:
$counts = Posts::get( array( 'month_cts' => 1, 'status' =>
Post::status( 'published' ) ) );
If plugins use our API which abstracts DB details, upgrades will be
much less painful.
--Blake
Andy C
the required changes were indeed minimal.
Thanks to Blake and Rick for correcting my knowledge.
Stanley FONG
I wrote a little theme function to display posts either 1, 2 or 3 years ago (with parameters such as a time window, e.g. display posts between 1 year less 2 weeks to 1 year ...etc). How do I use the lastest API to do the same thing?
Blake Johnson
> A quick lesson to me please, not capable of having my own sql query nor to
> use API yet I'm.
>
> I wrote a little theme function to display posts either 1, 2 or 3 years ago
> (with parameters such as a time window, e.g. display posts between 1 year
> less 2 weeks to 1 year ...etc). How do I use the lastest API to do the
> same thing?
Posts::get() accepts 'before' and 'after' parameters. So you can do
$begin_date = HabariDateTime::date_create( foo );
$posts = Posts::get( array( 'before' => $begin_date, 'after' =>
$begin_date->modify( '+2 weeks' ) ) );
--Blake
rick c
use them, really should be documented in the wiki.
Rick
Arthus Erea
http://wiki.habariproject.org/en/API/overview
Stanley FONG
Stanley FONG
Blake Johnson
> It would be nice if someone would post a quick fix to
> $time_ago = intval((time()-strtotime($post->pubdate))/86400)
>
> that would save me from writing the theme functions again.
>
That code needs only a minimal change:
$time_ago = intval( ( time() - $post->pubdate->int ) / 86400 );
--Blake
Stanley FONG
rick c
being stored as HabariDateTime, which extends PHP's DateTime class. If
you look in HabariDateTime.php, particularly at the __get() function,
you'll see 3 particular properties that can be retrieved:
$date->clone - returns a clone of the object
$date->sql - returns the object formatted in a way suitable for use in
sql queries
$date->int - returns the integer value of the object in seconds
$date->mon0 - returns a zero padded 2 digit month number
$date->mday0 - returns a zero padded 2 digit month day number.
Other properties that can be retrieved are the same as those listed at
http://www.php.net/getdate. In addition, a formatted version of a
HabariDateTime object can be retrieved by calling $date->format() or
$date->get(). $date->out() echoes a formatted date to the screen. The
formatting is determined using the same formatting characters used by
the PHP date() function, and listed at http://www.php.net/manual/en/function.date.php.
The default formatter is 'c', so calling $date->format() is the same
as calling $date->format( 'c' ). You can see all the methods of
HabariDateTime at http://doc.habariproject.org/api/ .
Rick
On Sep 23, 10:11 pm, "Stanley FONG" <sfon...@gmail.com> wrote:
> Thanks Blake, that cured it.
> Did I miss this in the wiki, where about is the documentation for this
> change?
>
Stanley FONG
rick c
Rick