How to convert "Day" and "Instant" DateObjects to the same format for TimeSeriesInsert?

Questions
, , ,
1

I have problems importing data from Excel and CSV. I tried different options, but still don’t quite understand how dates are transformed when imported to a Wolfram notebook. These are some questions:

  1. Once the data is imported to Tabular format (interpreted as String) I can use CastColumns. The type “Date”::[…] is not explained in the documentation. Example: “Date”::[“Integer32”, “Day”, “Gregorian”, None]. What can I use instead of None? How does Cast work for dates?
  2. Some of the date objects show simply as DateObject[{2026, 9, 24}, “Day”], but othes as: DateObject[{2026, 9, 14, 6, 0, 0}, “Instant”, “Gregorian”, 0.], even though they were originally imported from similar CSV files. How do I convert them to the same format?

The attached notebook shows the differences of date formats.

Any ideas on how to import data from CSV files more easily and how to change the date formats of large files easily?

Open in Wolfram Cloud ↗
2

Hi Ricardo,

Here we are dealing with the different parameters of dates, namely granularities and time zones. There are also calendars, time systems and date formats, but we will not talk about those here.

First note the InputForm structure of a date object:

In[1]:= Now // InputForm
Out[1]//InputForm= DateObject[{2026, 10, 6, 11, 35, 46.874883}, "Instant", "Gregorian", -5.]

What we see there is the structure DateObject[datelist, granularity, calendar, timezone]. There could also be two additional arguments for time system and date format.

Your olddata has “Instant” granularity and time zone -6. Your new data has “Day” granularity and time zone None. Check

In[12]:= ColumnTypes[olddata]
Out[12]= <|
  "Timestamp" -> "Date"::["Integer64", "Instant", "Gregorian", -6.], 
  "Value" -> "Real64"
|>

In[13]:= ColumnTypes[newdata]
Out[13]= <|
  "Timestamp" -> "Date"::["Integer32", "Day", "Gregorian", None],
  "Value" -> "Real64"
|>

Note how the “Date” type arguments have the same structure and order, except for the first one (the type of integer), which indicates how we are internally storing the dates (in some other calendar, say UnixTime for instants).

When you insert one time series into the other one, then we automatically select a granularity and a time zone for you. This depends on the order of arguments. Compare TimeSeriesInsert[newdata, olddata] and TimeSeriesInsert[olddata, newdata]. In the latter case, which is what you did, we project the day granularity into instants, selecting for each day the beginning instant. And then we instantiate the None time zone into the time zone -6, which can create shifts of six hours, moving some of the time stamps to the previous day.

Therefore it’s better if you first align the two time series to what you would like to have in output. For example, imagine you want to keep instants, but now all in time zone None. Then you can do something like this:

TimeSeriesInsert[ TimeSeries[olddata, TimeZone -> None], newdata ]

This is still going to introduce some 6 hours shifts in olddata, but will not spoil the day of newdata.

We can change date parameters in multiple ways, and I’ve mentioned only one: options. We can do it with CastColumns, or with date-related functions, like DateObject itself, or ConformDates.

Hope this helps,

Jose.

3

Thank you very much José. TimeSeries[ olddata, TimeZone->None] worked very well for my case.

Before asking the question I checked CastColumns, but the documentation only mentions “Date”::[…] and details are not provided. It would be good to have more information about how CastColumns ->“Date”::[…] works.

I usually import data from Excel or CSV files. I often convert my Excel files to CSV files for better results. It would be great that Wolfram could import dates from Excel and CSV in a uniform way. My olddata and newdata come from the same source so they should be in the same format.

After trial and error I also found that when creating the new TimeSeries from imported data I can add the function DateObject[#,“Instant”]. This way the newdata has the same format as the old data.