ISO date query

[Jon]

Hi,

I plan to use TW5-JsonMangler to import a diary as a .csv file into my wiki so that each row is a new tiddler with the date as the name.

The dates are in the format 20/06/07 rather than 2020/06/07.

Will my wiki recognise the dates correctly or do I need to somehow convert them first?

Regards

Jon

[TonyM]

Jon

You can import and reformat later. However I recommend converting the incomming date/title to one using the full serial date and put it in its own field e.g. published-date. You can then use all the tiddlywiki date list and format features on the date. You can leave the title as imported.

I/we can share the code if you want it.

Regards
Tony

[Eric Shulman]

It all depends on what you mean by "recognize the dates correctly".

Tiddlers have standard fields "created" and "modified", which get date values using "YYYY0MM0DD0hh0mm0ss0XXX" formatting (see https://tiddlywiki.com/#DateFormat and https://tiddlywiki.com/#Date%20Fields)

You can use these values in filters to identify and sort tiddlers (see https://tiddlywiki.com/#Filter%20Operators for "days", "eachday", "sameday", and "sort")

However, for your stated purpose, the imported date values will be used as the *titles* of the tiddlers.  Fortunately, this dates-as-titles usage will still sort nicely using simple alpha-numeric sorting, because your chosen format, "YY/0MM/0DD", puts the most-significant value first and uses zero-padded numbers.  If your dates were in a different format (e.g., MM/DD/YY), then this wouldn't work since a value like "6/7/20" would alpha-numerically come *after* something like "12/25/20".

I concur with TonyM's suggestion: use the YY/0MM/0DD date *as-is* for the title, but also create a fully-formatted custom field such as "published-date", using the same "YYYY0MM0DD0hh0mm0ss0XXX" format as the standard "created" and "modified" fields.  This will then allow you to use the above mentioned date-related filter operators with the custom field.

enjoy,

-e

[Jon]

Thanks Tony & Eric.

How do I convert  a date like 20/06/07 into 2020/06/07?

The dates are in a spreadsheet and range over 10 years. I first tried using find & replace eg.replacing '01/' with' 2001/' for 01/02/25 but that replaces the month field in subsequent dates.

Regards

Jon

[TonyM]

Quick answer

Longer one later,

 use split[/] to get each part than resemble them with a concatenation

Regards

Tony

[Jon]

Thanks Tony - I think I need to wait for the long answer 

Regards

Jon

[Hubert]

Hi Jon,

You can use this ugly macro below to "convert" any string like this: "20/06/07" into this: "2020/06/07", assuming that the earliest year in your range is the year 2000.

Paste this macro definition in a new tiddler:

\define UpdateDate(date)
<$vars
   y={{{ [<__date__>split[/]nth[1]add[2000]] }}}
   m={{{ [<__date__>split[/]nth[2]] }}}
   d={{{ [<__date__>split[/]nth[3]] }}}>
<<y>>/<<m>>/<<d>>
</$vars>
\end

And then below that, put your input dates in the macro like this:

<<UpdateDate 20/06/07>>

When you save the tiddler the output will be rendered to this: 2020/06/07

You can wrap this macro in a filter to change all dates in one go and then simply copy/paste the output.

The whole operation would probably be quicker and simpler using a text editor as these are just strings, not to mention that the above macro can probably be simplified to a one-liner. Anyway, just something quick.

Regards,

Hubert

[Jon]

Hi Hubert,

Thanks for this, which I'll be able to use down the line but at the moment, I wouldn't know how to apply this globally.

However, I've managed to find a solution using a text editor like Notepad ++. Instructions below for anyone else looking for this.

So, to convert 18/09/10 to 2018-09-10

Select 'Regular expression' in Search Mode

'Find what'       ([0-9]{2})/+([0-9]{2})/+([0-9]{2})

'Replace with'  20\1-\2-\3

there's a bit more info. here

Thanks

Jon

[TonyM]

Jon,

You can apply regex in tiddlywiki, so perhaps you can use your notpad++ method?

I will suggest once again (forcefully) if you want a global solution, then convert dates to the system date format then you can use $view date to display any format, including the relative date.

If your input date is 20/06/07 and its the title of a tiddler, try this on tiddlywiki.com

\define cc-date(date) 20$date$
\define date-yy-mm-dd(date) <$text text={{{ [[20$date$]split[/]join[]addsuffix[120000000]] }}}/>


:redate <<cc-date "20/06/07">> quick reformat for appearance
:<$macrocall $name=cc-date date={{!!title}}/> using title/field or variable
:<<date-yy-mm-dd "20/06/07">> reformat to serial date, also set to 12 noon


<$wikify name=journal-date text="""<$macrocall $name=date-yy-mm-dd date={{!!title}}/>""" output=text>
<$button>
<$action-setfield $field=journal-date $value=<<journal-date>>/>
set journal date
</$button>
</$wikify>


journal-date=<$view field=journal-date format=date template="YYYY/0MM/0DD"/> or <$view field=journal-date format=relativedate/>

Regards

Tony

[Jon]

Hi Tony,

Thanks for the suggestion, but my skills as they are mean I don't know how to apply this for all the records.

The notepad option works really well as I can just copy the date column from the spreadsheet (containing 3.5 thousand rows) and paste into Notepad ++ and apply the regex to the whole lot and then copy it back into the spreadsheet.

That means I can also have one column for the tiddler title eg. 07/06/20 and another to include the system date.

However, I hadn't twigged that I still need the extra  time zeros for the date to be recognised as a system date.

So, just to clarify, does 2020-06-07120000000 (i.e. '12' and 7 zeros) qualify as the system date?

In which case I can just add this in the regex.

Regards

Jon

[TonyM]

Jon,

12000000 is 12 Noon is is filled out to look as a standard date, Yes, I found setting it 00 hours is less helpful.

It is a valuable lesson to learn how to apply something to a set of tiddlers you can aplly to one tiddler;

<$button>
<$list filter="selected all relevant tiddlers">
<$action tiddlers...
</$list>
Button title
</$button>

Clicking the button will apply the action to all the tiddlers inside the list

Test the following on a copy of your wiki for safety as I do not have the data set to test

\define date-yy-mm-dd(date) <$text text={{{ [[20$date$]split[/]join[]addsuffix[120000000]] }}}/>

<$button>
<$list filter="selected all relevant tiddlers">

   <$wikify name=journal-date text="""<$macrocall $name=date-yy-mm-dd date={{!!title}}/>""" output=text>

   <$action-setfield $field=journal-date $value=<<journal-date>>/>

   </$wikify>
</$list>
Button title
</$button>

For safety the following list show which tiddlers the button will change, make the filters match

<$list filter="selected all relevant tiddlers">

</$list>

The bold text is the same method as my earlier post, it would set a correct journal-date for all tiddlers with a title yy-mm-dd 

Feel free to ask more Questions until you have a good solution.

Regards

Tony

[Jon]

Hi Tony,

thanks for the info.

Is the button text incomplete?  (<$action tiddlers...)  - I don't know enough to fill in any blanks!

Can I just check  - is it 6 or 7 zeros after the 12 as you've included both?

Thanks

Jon    

[Eric Shulman]

On Tuesday, June 9, 2020 at 9:55:36 AM UTC-7, Jon wrote:

Can I just check  - is it 6 or 7 zeros after the 12 as you've included both?

As I noted previously, the full format for a tiddler date field (i.e., "created" and "modified") is:

YYYY0MM0DD0hh0mm0ss0XXX

where

YYYY is a 4-digit year

MM  is a 2-digit month

DD is a 2-digit date

hh is a 2-digit hours

mm is a 2-digit minutes

ss is a 2-digit seconds

and 

XXX is a 3-digit milliseconds

except for YYYY, all the format code are preceded by a leading 0, indicating that the format is zero-padded (i.e., June is "06", not just "6")

Thus, noon today is:

20200609120000000

as you can see, there are 7 zeros following the "12"... 2 for minutes, 2 for seconds and 3 for milliseconds

also note that there is no other punctuation (no spaces, colons, slashes, or dashes)

-e

[Jon]

Thanks Eric,

I did refer back to that but forgot about the lack of puncutuation and 7 zeros it is.

Thanks for the explanation.

Regards

Jon