Feature request: add sfrtime ('%d /%m /%y') option in the Edit display format

[SilvioGrosso]

Hello everyone,

Please, add in the Browse Editor a new format for date columns, in the Edit display format options, that is:
strftime('%DAY /%MONTH /%YEAR', `MyColumn').

I am testing today's Windows daily build (3.10.99) on Windows 7 - 64 bit.

In short, I would like to have always displayed in the Browse editor my date columns as :
31/12/2017 (day/month/year) instead of 2017-12-31 (year-month-day)

At present, it only works when I write a Select statement.
E.g.:
SELECT strftime('%d /%m /%y', MyColumnWithDate) FROM Mytable

I have tried to paste my SQL Code directly in the Edit display format window but it is not possible to do so :-)

Thanks a lot indeed for considering this option !

[justinclift]

Any interest in making a Pull Request? 😄

It should be pretty simple to do a copy-paste-adjust of one of these:

• sqlitebrowser/src/ColumnDisplayFormatDialog.cpp

  Lines 11 to 44 in 9016bf6

  	ui->setupUi(this);
  	ui->comboDisplayFormat->addItem(tr("Default"), "default");
  	ui->comboDisplayFormat->insertSeparator(ui->comboDisplayFormat->count());
  	ui->comboDisplayFormat->addItem(tr("Decimal number"), "decimal");
  	ui->comboDisplayFormat->addItem(tr("Exponent notation"), "exponent");
  	ui->comboDisplayFormat->addItem(tr("Hex blob"), "hexblob");
  	ui->comboDisplayFormat->addItem(tr("Hex number"), "hex");
  	ui->comboDisplayFormat->addItem(tr("Octal number"), "octal");
  	ui->comboDisplayFormat->addItem(tr("Round number"), "round");
  	ui->comboDisplayFormat->insertSeparator(ui->comboDisplayFormat->count());
  	ui->comboDisplayFormat->addItem(tr("Apple NSDate to date"), "appleDate");
  	ui->comboDisplayFormat->addItem(tr("Java epoch (milliseconds) to date"), "javaEpoch");
  	ui->comboDisplayFormat->addItem(tr("Julian day to date"), "julian");
  	ui->comboDisplayFormat->addItem(tr("Unix epoch to date"), "epoch");
  	ui->comboDisplayFormat->addItem(tr("Windows DATE to date"), "winDate");
  	ui->comboDisplayFormat->insertSeparator(ui->comboDisplayFormat->count());
  	ui->comboDisplayFormat->addItem(tr("Lower case"), "lower");
  	ui->comboDisplayFormat->addItem(tr("Upper case"), "upper");
  	ui->labelDisplayFormat->setText(ui->labelDisplayFormat->text().arg(column_name));
  	formatFunctions["lower"] = "lower(" + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["upper"] = "upper(" + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["epoch"] = "datetime(" + sqlb::escapeIdentifier(column_name) + ", 'unixepoch')";
  	formatFunctions["javaEpoch"] = "strftime('%Y-%m-%d %H:%M:%S.', " + sqlb::escapeIdentifier(column_name) +
  	"/1000, 'unixepoch') || (" + sqlb::escapeIdentifier(column_name) + "%1000)";
  	formatFunctions["winDate"] = "datetime('1899-12-30', " + sqlb::escapeIdentifier(column_name) + " || \" days\")";
  	formatFunctions["appleDate"] = "datetime('2001-01-01', " + sqlb::escapeIdentifier(column_name) + " || \" seconds\")";
  	formatFunctions["julian"] = "datetime(" + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["round"] = "round(" + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["hex"] = "printf('0x%x', " + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["octal"] = "printf('%o', " + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["exponent"] = "printf('%e', " + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["hexblob"] = "hex(" + sqlb::escapeIdentifier(column_name) + ")";
  	formatFunctions["decimal"] = "printf('%d', " + sqlb::escapeIdentifier(column_name) + ")";

And yep, this is me trying to get you started with DB4S development bits as you have lots of useful suggestions. 😄

[SilvioGrosso]

Hello Justin,

Any interest in making a Pull Request?

Unfortunately, currenlty, I only know a bit of Python (really very basic stuff...).
My daily job is : "Plant pathologist guy" :-)

For sure, as soon as I know much better Db Browser for Sqlite, I can upload some Video Tutorials on YouTube :-)
I have edited the last ones with Shotcut [1], a cool open source video editor, which, incindentally, sports a QT GUI : It runs on Linux as well.

[1] https://www.shotcut.org/

[justinclift]

Cool. No worries at all. And yep, more video's sounds good. 😄

[justinclift]

Errr OpenShot or ShotCut?

[chrisjlocke]

Out of interest, if a display format is set for a column, should that affect the data input method for that field?

If a field is set to 'default', then typing into a cell types that data into that cell. If a column has a display format assigned to it, then typing into a cell no longer types that data into that cell. Typing in the 'Edit Database Cell' dock works, but this seems more 'time consuming' and laborious.
Additionally, if a column has a date format assigned to it, then typing a date into that cell should work - SQLite doesn't really care what data goes into what field. However, DB4S doesn't allow this - the data is entered, but then resets back to NULL.

I don't mind opening an issue for this (with screencast, which can explain stuff better than my mumblings), but wasn't sure what was 'normal behaviour' before I did...

Also, I noticed the 'choose display format' dialog was 'read only' as the settings are hard coded in DB4S. Wouldn't it make sense for a 'custom' option or to allow the formats to be editable, which would allow this type of issue to be resolved directly by the user - they could enter whatever format they wanted.

[chrisjlocke]

Any interest in making a Pull Request?

So I've cloned the repository, amended the file and GitHub Desktop has noticed the change. How do I make a pull request now? In GitHub Desktop, there is a 'Commit to master', but is that THE master, or MY master? *blank look*

[justinclift]

@chrisjlocke Did you clone from your fork, or from the sqlitebrowser/sqlitebrowser repo?

It sounds like it's going to add a new commit to the master of whichever repo you cloned. 😄

[SilvioGrosso]

Hello Justin ! ,

To record my video I run : Obs Studio (https://obsproject.com/download), which is open source and multi-platforms.

I edit my video with Shotcut which runs both on Linux and Windows.
Compared to OpenShot, IMHO, it is much more stable (at least on Windows...).
In the long past, on Ubuntu, I have tried OpenShot too.
Lately, Kdenlive has been ported to Windows and in the future I migth give it a try :-)

IMHO, for video editing stuff, the best is DaVinci Resolve, which is free (not open source though) to download for everyone:
https://www.blackmagicdesign.com/products/davinciresolve/

[chrisjlocke]

Did you clone from your fork, or from the sqlitebrowser/sqlitebrowser repo?

Yes, from the main repository.

I'll fork off. (ho ho ho)

[MKleusberg]

@SilvioGrosso Just added the "Date as dd/mm/yyyy" display format. Can you check one of the next nightlies to see if it does the job as expected? 😄

[SilvioGrosso]

Hello @MKleusberg

Just tried this new option with today's build on Windows 10 (64 bit) through the x64.exe installer.
It works as expected ! :-)

I have also tested this new date feature (dd/mm/yyyyy) with SqliteBrowser Projects:
when you save a new project and you reopen it this new date format is persistent among different sessions of work (in short, it is applied by default on your projects).

THANKS a lot indeed !

As far as I am concerned, you can close this ticket ;-)

[justinclift]

Awesome. Great work guys. 😄