When export date/time to excel, change the time(mm:ss)..

Hi all.

I have a problem.

Now, time is 2017-02-05 21:35:32.

But I want to export 2017-02-05 21:00:00 except minute, scecond.

But when I use writetable(to .xlsx), time changed 2017-02-05 21:35:32 in excel.

   t = datetime('now','TimeZone','Asia/Seoul','Format','yyyy-MM-dd HH:00:00') % 2017-02-05 21:00:00 
   T = table(t); % 2017-02-05 21:00:00 
   writetable(T,filename); % 2017-02-05 21:35:32

What is problem in this code?

Answer 1

[Peter Perkins]

Use dateshift to round the datetimes down to the start of the hour before saving to the file.

[dpb]

I figured there had to be some better way than recasting the string form, Peter, but I couldn't figure it out from the documentation path not knowing datetime very well yet.

I find the documentation for it exceedingly difficult to find stuff in since the functions/methods are all separated out into subsets and there's no single place that has them all. I kept looking for something apropos when writing the above and never stumbled across dateshift on all the links I pursued and if certainly wasn't a name that came to me to do lookfor with a useful pattern/substring.

Not sure what it needs as a fix, but it's not easy to use as is imo...

ADDENDUM

I guess as one fix would be one link that has all the functions/methods associated with datetime visible in a list at a high level in the tree in addition to the various subsets under the different kinds of operations listed.

It again goes back to the issue of trying to write documentation from a descriptive viewpoint only rather than normative definition of the language...TMW tries to anticipate usage and write to that rather than starting and exhaustively listing and defining syntax and methods. Is in line with the idea of easy to use if happen to match up with a given user's search or idea of what it is trying to do, but that doesn't match up it's not always easy to figure out where to go next when first search or two doesn't lead to nirvana. Hence, most users just quit looking and ask for help first...

[Peter Perkins]

I hear what you're saying. There's a tension between doc that is a giant reference manual and impenetrable for many, and doc that's completely examples and is more difficult to find specific details. We try to find a balance, but I hear what you're saying.

The page that I think you needed to find is this one. I'll pass your feedback on to the doc team. Thanks.

[dpb]

Thanks, Peter, always appreciate hearing back. As a note I found that link by searching for dateshift from your answer. I did the backwards search because I was curious as to where the reference lived. I never thought (and really still don't quite think) that what I was looking for to answer OP's question as being related to Arithmetic and Plotting so never tried that link.

I also didn't recognize what was looking for from the short description under command window help datetime that does at least list (what I presume is comprehensive) the available methods/functions as it simply says

dateshift - Shift datetimes or generate sequences according to a calendar rule.

I guess after the fact I can see how rounding to hours could be construed to be covered in that description but certainly didn't ring the bell when I scanned it while crafting the Answer I gave OP. I was sure there had to be something better than the kludge I showed but definitely wasn't able to find it.

I do grok the TMW dilemma...being a fossil that started in mainframe days of the Philco 2000 with nuclear design application codes whose user manuals ran to hundreds of pages simply for building input decks and FORTRAN language manuals that were basically a minimal recasting of the Standard or vendor implementation of the day, I certainly do understand what precise documentation can run into. I also observed over time after the advent of the user terminal first, then WYSWIG editors and the evolution from "roll your own" FORTRAN to RAD environments like Matlab that the willingness and ability of new hires to read and comprehend the documentation that we old-timers took for granted became less and less something they could do without extensive remedial help in learning how to actually parse the descriptions. That tendency I think has continued to evolve towards the expectation that everything should be transparent--well, some things just aren't.

Answer 2

[dpb]

What's wrong is the confusing nature of what the 'Format' property actually does with datetime variables; it only affects the output display of the time value, NOT the value of the time stored...example--

>> T=table(datetime('now','TimeZone','Asia/Seoul','Format','yyyy-MM-dd HH:00:00'));
>> t=T.Var1  % looks like an even hour, doesn't it?
t = 
 2017-02-05 23:00:00
>> whos t  % is a datetime object, too...
Name      Size            Bytes  Class       Attributes
t         1x1               123  datetime              
>> t.Format  % the format is as noted to hours only
ans =
yyyy-MM-dd HH:00:00
>> t.Format='default'  % now change that property
t = 
 05-Feb-2017 23:39:46
>>

and Voila! -- the actual time stored is revealed; the Format property hid what was really there.

You'll have to convert the time values you have to either datenum or datetime values by converting to the actual time values wanted. One way (not sure it's necessarily the most efficient) is to convert the output string with the desired format back to numeric internal form to get those values to be output.

Example--

>>t=datetime('now','Format','yyyy-MM-dd HH:00:00');  % a current time value
>> t.Format='default'    % what is actual?  (Does have MM:ss as expect)
t = 
 05-Feb-2017 09:02:48
>> t.Format='yyyy-MM-dd HH:00:00';  % set format to remove MM:ss
>> datetime(char(t))                % convert that string back to datetime
ans = 
 05-Feb-2017 09:00:00
>>

NB: Final result in default format show 00:00 for MM:ss; successfully rounded to hours only as desired.

>> t
t = 
 2017-02-05 09:00:00
>>

[Robert]

The question was to not export minutes and seconds.

Your answer exports 2017-02-05 09:00:00 while the question asks for 2017-02-05 09

How can that be achieved?

[dpb]

As the note/text says, it was an example for the OP to mung upon as chose/needed/wanted to match need illustrating the way to get whatever format is wanted.