Column Formulas Questions

[meteorquake]

Actually these seem somewhat related to the thumbs-with-details topic on customisable details, and the customisable tooltips, except those can include linefeeds. Thus eg
column1:=IFS(EXTENSION(name:)==".jpg",size:,EXTENSION(name:)==".mp3",artist:) add-column:column1
It could be improved by an IN() function, e.g.
EXTENSION(name:) IN(".jpg",".png",".bmp")
although perhaps easier as
ISIN(EXTENSION(name:),".jpg",".png",".bmp")
which is usually more familiar for people than regular expressions and may well be faster to execute
and also linefeed LF() or \n to become useable for multiline output in thumbs-with-details and tooltips
David

[meteorquake]

These are some of my further ruminations...
if just like column1:= etc you had formatters then these column formulae could actually do all the complex styling too
e.g.
fmtbold:=ISIN(EXTENSION(name:),".jpg",".png",".bmp")
fmtforecolor:=IF(ISIN(EXTENSION(name:),".jpg",".png",".bmp"),'#ff0000')
and so on. If there was such styling, then if the formulae could reference the prior row, then the Transition Formatting could also be achieved, e.g.
fmtbackcolor:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),'#ff0000') --- mark alphabet transition
fmtforecolor:=IF(datemodified:-datemodifiedprior>1/1440,'#ff0000') sort:date --- mark items as red that differ by 15 mins from their prior item (or however date differences are computed)
doing that it could be more efficient to avoid repeat calculations if the styling is all rolled into 1; so for the alphabet transition
fmt:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),'backcolor:#ff0000;bold;forecolor:#ffffff')
or perhaps predefined styles with a name -
fmt:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),'style:alphabettransition')
or maybe a transient name that's cleared on the search closure -
fmt:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),TEMPSTYLENAME('backcolor:#ff0000;bold;forecolor:#ffffff'))
perhaps that could happen implicitly without the explicit function.
Hopefully there may be some useful thoughts in this,
David

[anmac1789]

These are some of my further ruminations...
if just like column1:= etc you had formatters then these column formulae could actually do all the complex styling too
e.g.
fmtbold:=ISIN(EXTENSION(name:),".jpg",".png",".bmp")
fmtforecolor:=IF(ISIN(EXTENSION(name:),".jpg",".png",".bmp"),'#ff0000')
and so on. If there was such styling, then if the formulae could reference the prior row, then the Transition Formatting could also be achieved, e.g.
fmtbackcolor:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),'#ff0000') --- mark alphabet transition
fmtforecolor:=IF(datemodified:-datemodifiedprior>1/1440,'#ff0000') sort:date --- mark items as red that differ by 15 mins from their prior item (or however date differences are computed)
doing that it could be more efficient to avoid repeat calculations if the styling is all rolled into 1; so for the alphabet transition
fmt:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),'backcolor:#ff0000;bold;forecolor:#ffffff')
or perhaps predefined styles with a name -
fmt:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),'style:alphabettransition')
or maybe a transient name that's cleared on the search closure -
fmt:=IF(LEFT(name:,1)!==LEFT(nameprior:,1),TEMPSTYLENAME('backcolor:#ff0000;bold;forecolor:#ffffff'))
perhaps that could happen implicitly without the explicit function.
Hopefully there may be some useful thoughts in this,
David

I am really confused following this post, would it be possible for you to demonstrate for some of the examples above? There could be something useful in there that maybe I didn't understand fully. Thanks

FORMAT_FILETIME_UTC(filetime,[format]) - format a windows filetime in UTC

How do I use this?

[raccoon]

FORMAT_FILETIME_UTC(filetime,[format]) - format a windows filetime in UTC

How do I use this?

See this post. -> viewtopic.php?f=12&t=12267#p50709

[anmac1789]

FORMAT_FILETIME_UTC(filetime,[format]) - format a windows filetime in UTC

How do I use this?

See this post. -> viewtopic.php?f=12&t=12267#p50709

I'm seeing a column that looks vastly different. I used this command:

Code: Select all

"IMG_7789" pic: column1:=FORMAT_FILETIME_UTC:(date-created:,"YYYYMMDDTHHmmss") addcolumn:column1

Screenshot 2022-12-08 211239.jpg (38.7 KiB) Viewed 8775 times

And, using this command,

Code: Select all

"IMG_7789" pic: column1:=format-filetime-utc:$date-created:,"YYYYMMDDTHHmmss" addcolumn:column1

The result I get is,

Screenshot 2022-12-08 211613.jpg (40 KiB) Viewed 8775 times

[void]

Actually these seem somewhat related to the thumbs-with-details topic on customisable details

I would prefer using the standard semicolon (;) delimited wildcard filter to define the filter to match files, then use the column formula or preprocessor for formatting.
(preprocessor would be better for dealing with text)

Thanks for the suggestion.

I am really confused following this post, would it be possible for you to demonstrate for some of the examples above?

The two most useful examples I have seen for column formulas:
Find duplicates in two directories:
<"C:\path\to\folder1" col1:=A> | <"C:\path\to\folder2" col1:=B> dupe:size;!col1

Sorting or comparing more than 3 properties.
c:\windows\* column1:=TEXTJOIN(";",TRUE,size:,formatfiletime(dm:),formatfiletime(dc:),formatfiletime(da:)) addcolumn:column1 sort:column1

I don't see column formulas working for color filters or custom tooltips / views filters.
It's too hard to define the filters with column formulas.
There's easier methods available.
Column Formulas would be fine for formatting tooltips and other displayed information.

FORMAT_FILETIME_UTC(filetime,[format]) - format a windows filetime in UTC

How do I use this?

Show date modified in UTC in column 1 with the default date/time formating:
addcolumn:column1 column1:=FORMAT_FILETIME_UTC(dm:)

Show date modified in UTC in column 1 as ISO 8601:
addcolumn:column1 column1:=FORMAT_FILETIME_UTC(dm:,"YYYYMMDD\THHmmss.SSS")

[void]

See this post. -> viewtopic.php?f=12&t=12267#p50709

I'm seeing a column that looks vastly different. I used this command:

That post is using the Search Preprocessor which is similar to Column Formulas.
The same functions are supported, but the syntax is different.
The Preprocessor is designed for text, whereas Column Formulas is designed for numbers/values.

The multi-file renamer uses the preprocessor which this post was about.

[raccoon]

Would it be reasonable / user friendly to make all DATE datatype columns/variables automatically support a *-UTC suffex?
$dm-utc:

Would it be reasonable / user friendly to make all DATE datatype columns/variables automatically support a parameter for specifying format? or would that interfere with math equations?
col1:=$dm-utc:yyyyMMdd\THHmmss

You could do something similar with all SIZE datatypes for expressing KB/MB/GB suffex and rounding format, etc.

[void]

I've noticed some of you are using $dm: so I will add support for $property-name:
The $ isn't actually needed for column formulas.

dm: is a FILETIME which is already represented in UTC, so it makes it a little confusing.
I think this should be handled by the format function, perhaps it should have been a variable to formatfiletime()?

For example:
formatfiletime(dm:,"yyyyMMdd\THHmmss","local")
formatfiletime(dm:,"yyyyMMdd\THHmmss","utc")

I'm fine with a separate function as it is.

I do like the shorter syntax $dm-utc:yyyyMMdd\THHmmss, so maybe something could be done here.
I still think it needs to be a function call.
Maybe as a function aliases? fft-utc($dm:,yyyyMMdd\THHmmss)

For now, for size formatting, you'll need to use formatsize(size:,size-format-type)
where size-format-type is 0=auto, 1=bytes, 2=KB, 3=MB

Thank you for the suggestions.

[anmac1789]

LOL thats what I thought the $ sign was confusing me because I've never seen it used before in search syntax

Also I live in eastern timezone (UTC -5) but everything is showing -4 hours for the difference between local time and UTC time instead of 5 hours

[void]

Also I live in eastern timezone (UTC -5) but everything is showing -4 hours for the difference between local time and UTC time instead of 5 hours

What is your column formula?

[anmac1789]

Also I live in eastern timezone (UTC -5) but everything is showing -4 hours for the difference between local time and UTC time instead of 5 hours

What is your column formula?

it is column1:=FORMAT_FILETIME_UTC(dc:)

[void]

The UTC time should be correct.

No adjustment is made to the date/time when formatting with FORMAT_FILETIME_UTC.
The raw filetime is already in UTC and formatted as is.

Maybe it's a day light saving offset?
Does right clicking the file and clicking Properties show the same local date created time as Everything?

[anmac1789]

The UTC time should be correct.

No adjustment is made to the date/time when formatting with FORMAT_FILETIME_UTC.
The raw filetime is already in UTC and formatted as is.

Maybe it's a day light saving offset?
Does right clicking the file and clicking Properties show the same local date created time as Everything?

For example, when I turn OFF DST in windows 11 settings -> Time & Language -> Date & Time -> "Adjust for daylight saving time automatically" turned off, then view the folder/file it is 7:01:25 AM (local time) and UTC time in column 1 is 12:01:25 PM (difference of +/-5 hours)

when I turn ON DST in windows 11 settings -> Time & Language -> Date & Time -> "Adjust for daylight saving time automatically" turned on, then view the same folder/file it now shows 8:01:25 AM (local time) and UTC time in column 1 is still 12:01:25 PM (difference of +/-4 hours)

[void]

Sounds like it is day light savings time offset.

These are the expected results.

Does the local time in Everything match what is shown under right click -> Properties?

[anmac1789]

Sounds like it is day light savings time offset.

These are the expected results.

Does the local time in Everything match what is shown under right click -> Properties?

yes, it matches the properties dates and times of a file or folder matches the same details under everything

[raccoon]

(Please note that FAT filesystems do not store the time in UTC but in localtime, and so translations between timezones or UTC do not compute to the actual historic moment in time, since there is no telling the offset or locale or regional DST month/day).

[anmac1789]

(Please note that FAT filesystems do not store the time in UTC but in localtime, and so translations between timezones or UTC do not compute to the actual historic moment in time, since there is no telling the offset or locale or regional DST month/day).

The only filesystems I work with are NTFS and ExFAT, I have used FAT systems before but they are hugely problematic for file management.

[raccoon]

It looks like exFAT also uses LocalTime and not UTC, with extension UtcOffset fields which may or may not contain an offset value (index of 15-minute increments) to aid with translation to UTC. Cameras and other embedded devices are unlikely to utilize this field, and I don't know if special API are needed by Windows software in order to read and preserve these offset values in copy/move routines, or whether Windows 7 and 8 have full support for exFAT fields.

The exFAT OffsetValid field tells whether the OffsetFromUtc field contains UTC offset data or not.

As an aside, NTFS has timestamp granularity of 0.0001 second, exFAT of 0.01 second, and FAT of 2.0 seconds.

[anmac1789]

It looks like exFAT also uses LocalTime and not UTC, with extension UtcOffset fields which may or may not contain an offset value (index of 15-minute increments) to aid with translation to UTC. Cameras and other embedded devices are unlikely to utilize this field, and I don't know if special API are needed by Windows software in order to read and preserve these offset values in copy/move routines, or whether Windows 7 and 8 have full support for exFAT fields.

The exFAT OffsetValid field tells whether the OffsetFromUtc field contains UTC offset data or not.

As an aside, NTFS has timestamp granularity of 0.0001 second, exFAT of 0.01 second, and FAT of 2.0 seconds.

Is there a way to set /time_format=FORMAT_FILETIME_UTC(for all 3 dates and times) ? in addition to normal local time. For ex, local time (UTC time) using this kind of /time_format=hh:mm:ss tt (FORMAT_FILETIME_UTC(for all 3 dates and times)) ?

[void]

Is there a way to set /time_format=FORMAT_FILETIME_UTC(for all 3 dates and times) ?

No.

Everything will show the date/time with the system time zone.

Consider setting your system time zone to UTC (Coordinated Universal Time)

[Michi]

Hi!

A question regarding column formulas: I'm using this search

w:\folder\ regex:tags:[a-z] column0:=REGEX_EXTRACT(REMOVE_EXTENSION(name:),"DE-DEG.*") addcolumn:column0

which adds column0 to the current result list.

However, when performing a new, completely different search, column0 is still visible.
Is there an option that removes this additionally column again?

Michael

[void]

Hi Michael,

When your search includes addcolumn: or columns:, Everything goes into temp-column-mode.
Your current columns are backed-up before entering temp-column-mode.

When the search no longer contains addcolumn: or columns:, Everything will exit temp-column-mode.
When exiting temp-column-mode, Everything will restore your previous columns.

In other words, if you remove a column while in temp-column-mode, Everything will forget this change when you change your search.



Please make sure your search does not include any addcolumn: or column: searches.
Remove the column 0 column (right click the column header and click Remove column)

Everything should now remember column 0 is hidden.

[Michi]

Thanks for this comprehensive reply!

Could resolve this now!

Thanks,
Michael