I used Excel to convert from date and time to seconds using the formula datetime * 86400. For example, 24/04/2023 8:00 AM is converted to 3891484800. What would be the correct format to perform this calculation in my workspace? Thanks for the assistance.
Question
How do I convert from date and time input to seconds in my workspace?
+67- Celebrity
What are you wanting to do with the time in seconds? Excel does some interesting things with dates, in your above example, the date value is actually 45040.33 and Excel is converting it to 24/04/2023.
There could well be a much easier solution to your problem using some of the datetime transformers in FME
Usually when you deal with time in seconds, you use epoch, which the number of seconds since January 1st, 1970 00:00:00 UTC. Your converted value is April 25, 2093 in Epoch
I'm using the datetime in the Google Distance Matrix API for departure time. What you mentioned in the last paragraph is in line with what the documentation says.
"Specifies the desired time of departure. You can specify the time as an integer in seconds since midnight, January 1, 1970 UTC." - Google Distance Matrix API documentation
+67- Celebrity
I'm using the datetime in the Google Distance Matrix API for departure time. What you mentioned in the last paragraph is in line with what the documentation says.
"Specifies the desired time of departure. You can specify the time as an integer in seconds since midnight, January 1, 1970 UTC." - Google Distance Matrix API documentation
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
The preview data output in the DateTimeConverter Transformer differs from the Excel output. The Excel output is 3891436320 for 23/04/2023 18:32.
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
When you add your excel reader, under parameters, what is the data type for the date field?
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
In the Excel file, the field is a custom field using the format dd/mm/yyyy h:mm. In the workspace, the DateTime field has the below format.
The DateTime converter is giving a rejected output.
The error message is "DateTimeConverter_<Rejected> (TeeFactory): DateTimeConverter_<Rejected>: Termination Message: 'DateTimeConverter output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''"
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Are you able to share an example of the excel sheet? Mainly just a couple of rows with the date in the above format
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
The Excel file is attached.
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
ignoring how excel formats the data, for the moment, looking at the raw formatted data in FME,
The first four rows are only timestamps, in the format of %H%M%S
The last two rows are datetimestamps, in the format of %Y%m%d%H%M%S
Further, when you do look at the formatted values in excel, you see that the day is 0 January, an invalid date.
Ignoring the first four lines, as they are invalid, you will need to configure the DateTimeConverter to the following to get epoch
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
I used the format; however, the preview value differs from the Excel output.
Also, do you know why there is a rejected output from the DateTimeConverter and a column name fme_rejection_code with the record 'INVALID_INPUT'?
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Its rejecting the first four features which are not stored in excel as datetimes
The values are different because the method you're using in Excel is not calculating an Epoch. You're multiplying 20230423183200 (which is in %Y%m%d%H%M%S - first four digits are the year, 2023, followed by the month in two digits, 04, then day, 23, then hour minute and second, 18320) by 86400
In FME you are converting that representation of a datetime into a valid Epoch
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Thanks for the clarification. I'm not receiving the duration in traffic response from the Google Distance Matrix API when I run the workbench. When I use the Excel conversion in a Python script, I receive the duration in traffic in the response.
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
You might want to blur out your key in the above image as anyone could use it to query and rack up charges against the matrix api
The value FME outputs is a float (has a decimal place), it looks like the Google API is expecting an integer (no decimal place).
The date you're passing through in the above screenshot is 70 years in the future - this is a useful site to convert between Epochs and timestamps
https://www.epochconverter.com/
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Thanks, I've replaced the screenshot and thanks for the clarification. I used the epoch converter and ran the Google Distance API using Python script, and I received the duration in traffic. I'm not sure why running the workbench with the time format is not outputting the duration in traffic.
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Can I please get your help in ascertaining why the translation failed?
I adjusted the DateTimeConverter however the translation is not going through.
The error message received is 'DateTimeConverter_<Rejected> (TeeFactory): DateTimeConverter_<Rejected>: Termination Message: 'DateTimeConverter output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation'''
The JSONFlattener 'An error has occurred. Check the logfile above for details'
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
The DateTimeConverter is failing because the input date format doesn't match what you've specified. You need to make sure the format of the date in the attribute you're converting, matches what you've specified in the DateTimeConverter
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
I've adjusted the DateTime input format in the DateTimeConverter Transformer to match the input DateTime format. I'm still receiving an error message.
Below is the input DateTime.
Below is the DateTimeConverter Transformer.
Do you know why I'm receiving a rejected feature? The error message is DateTimeConverter_<Rejected> (TeeFactory): DateTimeConverter_<Rejected>: Termination Message: 'DateTimeConverter output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''.
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
You'll need to check what the format is on the feature, don't trust what a UI element is displaying it as.
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
I adjusted the DateTimeConverter format to match the input time shown in the Translation log, and I'm still receiving a rejected output.
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Your input format is wrong, it should be %H%M%S, not %l%M%S
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Even when I change the format, I still receive an error message.
+67- Celebrity
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Hmmm, can you try using a parameter fetcher before the DateTimeConverter and instead of referencing the parameter in the DateTimeConverter, reference the attribute the parameter value is on
For the datetime in your example, you'd use the following in the DateTimeConverter to convert to Epoch (UTC)
Some adjustments were made, and the transformer ran without the error being generated. The duration in traffic is not generated, though. I'm still trying to figure out why.