Question

Combining time and date

  • 21 September 2016
  • 1 reply
  • 13 views
ingalla
Rank
Badge +9

I have a data set that includes a date field with the date formatted as YYYY-MM-DD and a time period field where the time is given a number for every 15 minute interval.

For example between 00:00:00 and 00:14:59 the time-period variable value is 00 and for between 00:15:00 and 00:29:59 the time period value is 01. This number increases all the way up to 95 for every 15 minute interval within a 24 hour period.

I have created an Excel spreadsheet which correlates the time period with the value in the data as below:-

time timeperiod

00:14:59 00

00:29:59 01

I need to get a single field containing the time and date and can not work out the best way of combining the fields in each record. I was planning on using a feature merger on the time period field in both the data and spreadsheet, but am not sure on the best method of combining the date and time fields.

All suggestions welcome.

1 reply

takashi
Rank
Userlevel 2
Badge +17

Hi @ingalla, since you have created the correlation table already, I think the FeatureMerger would be a good choice. After merging a time string to each record, you can easily concatenate the date string  (YYYY-mm-dd) and the time string (HH:MM:SS), using the StringConcatenator (or AttributeCreator or AttributeManager). Assuming that the date and time strings are stored in attributes called "date" and "time", the expression would be:

@Value(date) @Value(time)

Another thought. Any datetime value can be processed with arithmetic operations if you convert it to the "number of seconds elapsed from the epoch", and the timeperiod values (00-95) can also be treated as numeric values. e.g. the beginning datetime (seconds) of a period = <date (seconds)> + <timeperiod> x 15 (min.) x 60 (sec.). So this procedure would also be possible.

  1. AttributeCopier: Copy the source date string (YYYY-mm-dd) to a new attribute (e.g. "_beg"), which will be converted to the beginning datetime of the period.
  2. DateFormatter: Convert the "_beg" to the number of seconds elapsed from the epoch. Use the "%s" format specifier.
  3. AttributeManager (or AttributeCreator): Calculate the beginning and ending datetime values for each period in seconds. i.e. _beg = _beg + <timeperiod> x 900; _end = _beg + 899
  4. DateFormatter: Convert the resulting datetime values (seconds) to datetime strings with a preferable format specifier. e.g. "%Y-%m-%d %H:%M:%S".

0684Q00000ArKFqQAN.png

Reply


Community Stats

28,670
Posts
109,365
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.