Date formatting in python or in PostgreSQL

20 July 2004   1 comment   Python

Mind That Age!

This blog post is 13 years old! Most likely, its content is outdated. Especially if it's technical.

Powered by Fusion×

I deviced a very simple benchmark through Zope as the web server. Two SQL Select statements that draws from a dummy database table which contains a table with loads of dummy entries with a timestamp field.

Conclusion is that Python's DateTime module is considerably slower than PostgreSQL's builtin date formatting function.

On the first experiment, I select the timestamp as a string and use Python's DateTime module to convert the date to a DateTime object then I apply the strftime() function to get the date printed in a formatted fashion (e.g. 04 July 02 20). The SQL looks like this:

SELECT name, time_stamp
FROM test_datetime_entries;

On the DTML output I apply the DateTime module with code like this:

<dtml-var "ZopeTime(time_stamp).strftime('%y %B %d %S')">

On the second experiment I used PostgreSQL's equally useful to_char() function to do the formatting in the SQL command so that formatted format is already prepared. The SQL looked like this:

SELECT name, time_stamp,
  TO_CHAR(time_stamp,'YY Mon DD SS') AS time_stamp_formatted,
FROM test_datetime_entries;

and it's corresponding DTML much simpler this time:

<dtml-var time_stamp_formatted>

Then I had a script that randomly stress tests either experiment. After about 5000 iterations the following average fetch times were noted:

Using DateTime module Using to_char() SQL function
3.556 seconds 1.201 seconds

Why is this important?

Generally, using the power SQL is better than using a programming language that has to work with the data because it's faster. But as a pattern it's less good. The database is responsible for reading and writing data, not to fiddle with the presentation; that's what you have the programming language for. These simple results prove that it's worth breaking that pattern and exploiting the SQL power more.

Follow @peterbe on Twitter

Comments

jose
gracias!!! me sirvio de algo este ejm
Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
Honesty and advertising on Gizmodo 18 July 2004
Next:
University results 22 July 2004
Related by Keyword:
Fastest *local* cache backend possible for Django 04 August 2017
How to do performance micro benchmarks in Python 24 June 2017
Django forms and making datetime inputs localized 04 December 2015
How do log ALL PostgreSQL SQL happening 20 July 2015
Benchmarking Autocompeter 12 April 2015
Related by Text:
Date formatting in Python or in PostgreSQL (part II) 19 April 2006
pg_class to check if table exists 20 April 2005
Fastest "boolean SQL queries" possible with Django 14 January 2011
Running simple SQL commands on the command line 08 January 2005
Using Lovefield as an AJAX proxy maybe 30 September 2015