To compare call log timestamps with deal notes, check fractional seconds, match the format string, and convert both records to the same time zone.
Key takeaways
- Check the position of digits after the decimal point.
- Test the format string against known input values.
- Record each system's stored and displayed time zone.
- Filter deal field edit history before comparing times.
A rep writes a note in the CRM about a pricing call. The recording system stamps the audio with its own time. When the two timestamps disagree by a few seconds, the review slows down, and someone has to decide which record to trust. That disagreement can come from how the timestamps are handled in the data, not from the call itself.
This post covers three mistakes with fractional seconds, which is the decimal part of a time value. The first is reading the digits after a decimal point as if they were a fixed unit. The second is letting a format string guess at its input. The third is ignoring time zone and daylight saving rules when times move between systems. After those three, the post shows how to check a deal's field edit history against a call time, and it ends with a checklist you can hand to whoever maintains the export.
The examples use PostgreSQL and SQLite because their official documentation states these rules directly. Each rule applies to the product named in its source. If your recording platform or CRM behaves differently, check that product's own documentation before you assume the same result. Nothing here is a claim about what any particular vendor does by default.
Digits after the decimal point are not automatically milliseconds
In PostgreSQL's formatting functions, the format SS.MS reads the digits after the point as a fraction of a second, so the position of each digit matters. The PostgreSQL formatting documentation gives a direct example: to_timestamp('12.3', 'SS.MS') is not 3 milliseconds, but 300, because the conversion treats it as 12 + 0.3 seconds.
That is the trap for a call log. Suppose an export writes a fractional part as a short decimal, and the person loading it expects the number to be a count of milliseconds. A value that looks like 3 milliseconds in the file becomes 300 milliseconds after conversion. That is a large error, and it is easy to miss. When you line that call up against a deal note, the two times will not match, and nothing on the screen says the parser caused it.
The same documentation shows that trailing zeros do not change the value. For the format SS.MS, the input values 12.3, 12.30, and 12.300 specify the same number of milliseconds. Padding digits to make a column look tidy does not change the result. What matters is where each digit sits relative to the decimal point.
To get three milliseconds, one must write 12.003, which the conversion treats as 12 + 0.003 = 12.003 seconds. In practice, the 3 has to sit in the third place after the decimal point. Writing it in the first place, as 12.3, gives a different time altogether.
The documentation also includes a longer example with two fraction fields: to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds. This shows that a format can read milliseconds and microseconds from separate parts of one value, and that the parts add together. If an export carries sub-second precision in more than one field, the format needs to name each part so you know what each group of digits means.
What to do with this in practice is straightforward. Before you load a call export, look at how its fractional part is written. Decide whether each part is a fraction of a second, a count of milliseconds, or a count of microseconds. Then write the format to match that choice. Test with a few known values, including one that ends in a zero and one that is only a few milliseconds, and check that the converted time matches what the recording shows. Do this before the full file is loaded, not after someone spots a mismatch in a deal review. For related preparation, use the sales data import checks.
Match the format string to the input
Match the format string to the input, because PostgreSQL's documentation shows conversions that skip characters from the input. The same formatting documentation gives two examples built on one input text, '2000y6m1d'. In the first, the format is 'yyyytMMtDDt'. In the second, the format is 'yyyy"y"MM"m"DD"d"'. The documentation says that both examples skip y, m, and d.
For call logs, the lesson is that a format string describes the input, and it is not a loose hint. If an export places letters, slashes, colons, or other separators inside its timestamps, write each one into the format on purpose. Then run the conversion on a sample that contains every separator that appears in the file, and confirm that each part lands where you expect. A sample that looks clean but leaves out an unusual separator will not tell you much.
The same page also covers week-based dates. It says: "In the context of an ISO 8601 week-numbering year, the concept of a 'month' or 'day of month' has no meaning." If an export includes an ISO 8601 week-numbering year, do not combine it with a month or day-of-month field as though it were a calendar date. Check which kind of year each column holds before you convert it, and label the column in your load notes so the next person does not have to guess.
Format mistakes are hard to see because the output still looks like a valid timestamp. A converted value can be in the right format and still carry the wrong meaning. That is why the test sample matters more than the eyeball check. Pick values where you already know the real time, run them through the same format you plan to use for the full export, and compare the results field by field.
Daylight saving rules are a separate source of mismatch
Daylight saving time rules are a separate source of mismatch, and SQLite's documentation notes one platform limit: "Non-Vista Windows platforms only support one set of DST rules." That statement appears in the SQLite date and time functions page. It applies to non-Vista Windows platforms, and it is the only platform rule this post draws from that page.
For sales ops, the practical point is that a call recorder, a CRM, and a reporting database can each store and display time zones in their own way. A rule that one system follows may not match what another system does. If two systems disagree about a time zone, the difference can make two correct records look as if they describe different moments. A gap like that does not by itself show that either record is wrong, so check how each side was converted before anyone questions a record.
A workable approach is to find out which time zone each system stores and which one it shows on screen. Convert every timestamp to one agreed zone before you compare anything. Pay particular attention to calls near a daylight saving change, and check those by hand. Once the zone is set, the fractional-second rules from the first section still apply, so check both parts of the time, not only the clock hour.
It also helps to record the zone decision in writing. A short note that says which zone the recordings use, which zone the CRM uses, and which zone the reports use can help when records are compared later. The note should name the platform and the version you tested, because a limit that applies to one platform does not automatically apply to another.
If an export has no zone field, say so in the load notes and record the zone you assumed. An assumed zone is still an assumption, and anyone reviewing the numbers should be able to see it.
Check a deal's field edit history against the call time
You can check a deal's field edit history against the call time, because the Freshsales help article says you can filter the fields by field name, across time periods, and updated by value. The article, titled "How to track Field Edit History of Deals?", is on the Freshworks support site.
Start with the field that a rep says changed after a call. Filter the history to that field name. Set the time period so it covers the call and the period afterward. Then filter by the updated-by value for the person who made the change. You now have a short list of edits that line up with the call window. Compare each edit time with the start time of the recording, after both have been converted to the same time zone and read with the fraction rules above.
The filter tells you which entries to look at. It does not replace reading the rest of the article, which is where you should confirm what each entry records before you rely on it in a timing discussion. If an entry shows a time that is off from the recording, check the conversion steps first. A parsing mistake can look like a real gap between a call and a note, and it is quicker to rule out than an argument about who said what and when. Use the deal field edit history guide alongside this comparison.
When the filter returns nothing, do not assume the call did not affect the deal. Widen the time period, confirm the field name matches the one in the CRM, and check the updated-by value. Each of those is a plain search problem, and it is quicker to check them than to redo the export.
Illustrative example: compare fractions within the same second
Suppose a call starts at 300 milliseconds into a second and a deal edit occurs at 20 milliseconds into that same second. After confirming both timestamps use the same zone and units, the edit precedes the call. These illustrative values do not establish why the field changed.
The lesson of this example is not that the rep was right or wrong. The first explanation people reach for, a late note or a bad call, is only one of several. A conversion error is easy to rule out with a known-value test, and it should be ruled out before anyone questions a person's record. Keep the test values and the results together, so the next person can repeat the check without starting over.
Fix the parsing first, then compare the records
Fix the parsing first, then compare the records, because a conversion error can look like a real disagreement between a call and a note. The steps below put the fixes in order. Each one is general practice, and each one depends on the documentation for your own systems.
- Pick one call whose real start time you know, and trace it through the export, the recording system, and the CRM note.
- Check how each fractional part is written, and decide whether it is a fraction of a second, a count of milliseconds, or a count of microseconds.
- Write the format string to match those units. Test it on known values, including the 12.3 and 12.003 pattern from the first section, and confirm the result is what you expect.
- Confirm that the format names each separator in the input, and that no ISO 8601 week-numbering year is combined with a month or day-of-month field.
- Convert all timestamps to one agreed time zone. Note which systems store a zone and which ones display it, and check any call near a daylight saving change first.
- Compare the corrected times with the deal's field edit history, filtered by field name, time period, and updated-by value.
- Write down each rule you applied, with its source, so the next export is handled the same way.
Once these steps are done, keep the written rules where the next person on the team can find them. Recheck them whenever a recording system, a CRM, or a reporting database is upgraded, since a change to any of them could affect how timestamps are read or displayed. A few careful checks at the start can prevent a long argument over a mismatched minute.
Today, take one known call and save its raw timestamp beside the converted value and the matching deal edit. Record the format and zone used so someone else can repeat your check.
Related guides: AI call scorecard checks: 5 rules before the forecast · Calendar availability: 4 tools for forecast call timing · Check 6 caption settings before the weekly sales call.
Questions about call log timestamps
Do trailing zeros change the fractional value?
Trailing zeros do not change the value in PostgreSQL's SS.MS format. Check digit position rather than the length of the fractional part.
What should I check when field edit history returns nothing?
Check the time period, field name, and updated-by value. An empty result alone does not show whether the call affected the deal.