PyDAL documentation for Date Formats '%Y-%m-%d' , '%m/%b/%Y %H:%M:%S' etc

[klaus z]

I am trying to extract todays etc entries from the Database.

rows = db((db.fritzbox.infolist_detail_name == 'infolist[0][NewMultimeterPower]/100 [W]') 

          & ( str(db.fritzbox.date_valid, '%Y-%m--%d') == str(datetime.date.today() )

         ).select(db.fritzbox.date_valid.year(), db.fritzbox.date_valid.month(), db.fritzbox.date_valid.day(), db.fritzbox.infolist_value

                , limitby=(0, 15), orderby=~db.fritzbox.date_valid).as_json()

But it seems I cannot format a timestamp field to a date format as marked red, what is not working. 

When looking into the  pyDAL Documentation Release 17.11, py4web Documentation Release 1.20210207.1 and WEB2PY COMPLETE REFERENCE MANUAL, 5TH EDITION I did not see the relevant part. I cannot imagine that I need to extract year, month, date separate.

Any hint is welcome. 

~Klaus

[Jim Steil]

I see a few issues

1. Are you missing a closing paren after - str(datetime.date.today() )  ?

2. I don't think you can format a date using the str function

3. There is an extra dash in your date format just before the day portion

if db.fritzbox.date_valid is type 'date' then:

rows = db((db.fritzbox.infolist_detail_name == 'infolist[0][NewMultimeterPower]/100 [W]') 

          & ( db.fritzbox.date_valid == datetime.date.today() )

         ).select(db.fritzbox.date_valid.year(), db.fritzbox.date_valid.month(), db.fritzbox.date_valid.day(), db.fritzbox.infolist_value

                , limitby=(0, 15), orderby=~db.fritzbox.date_valid).as_json()

if db.fritzbox.date_valid is type 'datetime' then I think you would have to use the year, month, day methods.

rows = db((db.fritzbox.infolist_detail_name == 'infolist[0][NewMultimeterPower]/100 [W]') 

          & ( db.fritzbox.date_valid.year() == datetime.date.today().year )

          & ( db.fritzbox.date_valid.month() == datetime.date.today().month )

          & ( db.fritzbox.date_valid.day() == datetime.date.today().day )

         ).select(db.fritzbox.date_valid.year(), db.fritzbox.date_valid.month(), db.fritzbox.date_valid.day(), db.fritzbox.infolist_value

                , limitby=(0, 15), orderby=~db.fritzbox.date_valid).as_json()

I think it would be great if there were a db.fritzbox.date_valid.date option, but I don't see that.  It would be a great feature request.

-Jim

[klaus z]

Thank you Jim, 

sorry for my errors in #1 and #3. I had an important appointment and wanted to get the question out before i left. 

So it seems there is no simple solution. If there is nothing, we should consider a feature request as this is standard in SQL. 

Even on stackoverflow there was little. 

But, there are the command:

'The strftime() method returns a string representing date and time using date, time or datetime object.' and

'The strptime() method creates a datetime object from a string.

https://www.programiz.com/python-programming/datetime/strftime

What I managed (with jupyter notebooks): 

---------------

rows = db((db.fritzbox.infolist_detail_name == 'infolist[0][NewMultimeterPower]/100 [W]') 

          & (True)

         ).select(db.fritzbox.date_valid

                , limitby=(0, 15), orderby=~db.fritzbox.date_valid)

print(rows)

fritzbox.date_valid 

2021-04-07 19:09:29 

2021-04-07 19:05:29 

2021-04-07 19:01:28 

2021-04-07 18:59:28

print(rows[0].date_valid.strftime("%m/%d/%Y, %H:%M:%S"))

04/07/2021, 19:09:29

print(rows[0].date_valid.strftime("%m/%d/%Y, %H:%M"))

04/07/2021, 19:09

strftime() allows to format. 

'Somehow' these commands should go into the DAL commands. I will investigate. But I am open for an answer from someone who sees the solution. 

As a side note, the 'fritz.box' thats a very common DSL router not only in Germany from the company AVM. I can retrieve data from the new and outdated boxes through python. It's possible to connect wireless 'switches' to monitor consumption and production of electricity going through the mains. 

I get the data and store it in a database. And I want an online graph for the values over time. 

~Klaus 

[Jim Steil]

Klaus

I think 'date' would be easy to implement in pydal.  It seems like you'd just have to duplicate what is done with year(), month() and day().  I did some testing with it but didn't have any luck coming up with a solution.  Maybe I'll go back and look at it a bit more.

-Jim

[Massimo]

Sorry I am late to catch up. If I understand this thread is asking to extend pydal to produce a python level function that maps into sql's strptime  and strftime. I am not sure why. The fact that SQL can do it, I am not convinced the SQL should do this. I think conversion are better dealt with within Python (because it is more powerful) and or at the html/js layer (because it is aware of actual locale). The server has only information about the server locale and not the user one. The locale of the server may be set to EN-US but the client (browser of the user) may be in a different language. Neither the SQL not python have the flexibility to handle this.

[klaus z]

Thats not an urgent issue. Personally I see it more as a strategic decision and that it would make sense to have such a formatting.

When looking back, i think it was around 1998 companies promised to create applications where databases can easily be exchanged. From what i know it never really worked (that easy). 

What happened in the past years, mainly because of license fees, the old established companies started trying to move away from Oracle etc and tried to install PostgreSQL etc. But as there are special functions in the database (e.g. like reporting tables, feeded through materialized view logs updated by triggers on the original operational tables (the exact wording for this is a litte different)) its very difficult to change the db to another vendor. Another issue are user interfaces - talking about Oracle - programs like Oracle Application Express (APEX). 

When I saw pyDAL a few years ago with Web2Py and got more used to it, mainly through Jupyter notebooks and the upcoming py4web, I realized the potential. Thats is, what was promised a long time ago (1998 ff).

Having used a 2 page cheat sheet for SQL and seeing the SQL-99 (or whatever newer SQL Standard) now, I tried to use DAL instead of SQL. There are still some areas in pyDAL like views which are missed (but there is also more in pyDAL) compared to the SQL standard. 

I see two reasons for having a such a timestamp formatting: 

(1) From this point of view I would have said, having a good overlap with pyDAL compared to whats available in SQL-Standard, it would be good. Thats as a general issue. (I someone would have asked me weeks ago I would have said timestamp formatting is there in DAL). 

(2) Coming from an application I am currently working, a D3/Vega-Lite real time graph. The sensor data is going into an SQL database. From there - best activated by an insert trigger but currently polling every 120sec -  new data should be provided through YATL to an HTML graph page. Initially I wanted to work with the insert timestamp in getting new data when comparing it with the current timestamp. The new data sent based on an insert trigger would be real realtime. Right now i am also using the database record ID instead of the timestamp for selecting new records/measurements. 

Re problems or better solutions, on server time and local time, i think it was Luca who suggested always to use UTC.  

Sorry that it got long. I am sure there are other ideas about this. It's Massimo who has the best overview and should finally decide. There are workarounds. 

~Klaus