I am trying to produce a system in Python by which it's possible to choose numerous rows from some tables, which should be formatted inside a user-defined way. Let us the table a has some posts, most of which incorporate a date or timestamp value. The consumer-defined format for every column ought to be saved in another table, and queried and put on the primary query at runtime.

Allow me to provide you with a good example: You will find techniques used in formatting to start dating ? column, e.g. using

SELECT to_char(column, 'YYYY-MM-DD') FROM table;

in PostgreSQL.

For instance, I would like the 2nd parameter from the to_char() builtin to become queried dynamically from another table at runtime, after which applied if it features a value.

Reading through the meaning from the table as a result isn't that a problem, instead of developing a database plan which may receive data from the interface that a person can choose which formatting instructions to use towards the different posts. The consumer should have the ability to select the user's group of posts to become incorporated within the user's query, along with the user's user defined formatting for every column.

I have been considering carrying this out within an elegant and efficient method for some days now, but with no success. Getting the consumer make the user's preferred definition inside a text area and including it inside a query would virtually generate an invite for SQL injection attacks (although I possibly could use escape() functions), and storing every possible combination does not appear achievable in my experience either.

It appears in my experience a stored procedure or perhaps a sub-choose works well here, though I've not examined it. Let us say you store a date_format for every user within the users table.

SELECT to_char((SELECT date_format FROM users WHERE users.id=123), column) FROM table;

Your mileage can vary.

Pull the dates out as Unix timestamps and format them in Python:

SELECT DATE_PART('epoch', TIMESTAMP(my_col)) FROM my_table;

my_date = datetime.datetime.fromtimestamp(row[0]) # Or equivalent for your toolkit

I have found a few benefits of this method: unix timestamps would be the most space-efficient common format (this method is effectively language neutral) and since the word what you are querying the database was more potent compared to underlying database, providing you with lots of options should you start thinking of doing user friendly formatting like "today", "yesterday", "a week agoInch, "June 23rd".

I'm not sure what kind of application you are developing but when it is something just like a web application which is utilized by multiple people I'd also consider storing your database values in UTC so that you can apply user-specific timezone configurations when formatting without needing to consider them for all your database procedures.