Date Picker fields require Unix Timestamps that reflect Midnight UTC - PHP Conversion

SOLVE
danhammari
Contributor

I finally figured out why I was getting an error when uploading my Unix Timestamp values for date picker fields to HubSpot AVI v3. It turns out that my local database stores Unix Timestamp values for midnight that are offset by local time zone. This means I am storing date values as midnight UTC + 7 instead of plain midnight UTC. This resulted in the following error message when I sent my millisecond Unix Timestamp values over to the HubSpot API:

 

API ENDPOINT: https://api.hubapi.com/crm/v3/objects/deals

HTTP METHOD: POST (create)

HTTP RESPONSE:

 

 

{
  "status":"error",
  "message":"Property values were not valid: [
    {
      \"isValid\":false,
      \"message\":\"1637650800000 is at 7:0:0.0 UTC, not midnight!\",
      \"error\":\"INVALID_DATE\",
      \"name\":\"quote_creation_date\"
    }
  ]",
  "correlationId":"eb4ca347-14fb-4313-89d8-dc3dc0e82161",
  "category":"VALIDATION_ERROR"
}

 

 

Unix Timestamps always represent the UTC timezone. I assumed that my local database was representing my date fields as the hour of midnight in UTC. Consequently, I wondered at the error message that HubSpot kept giving me. After reviewing the raw values in my database I realized that my colleagues had not compensated for UTC when converting whole dates to Unix Time. All my date-only fields are instead stored as midnight of my local time zone instead. Here is the logic I added to my PHP script to put my unix timestamp values to exactly midnight UTC:

 

$temp_date = $this->getAttribute( 'date_field' );
// detect if date is a timestamp
if( ctype_digit( $temp_date ) && strtotime( date( 'Y-m-d H:i:s', $temp_date ) ) === (int)$temp_date ){
  $temp_date = '@' . $temp_date; // prefix the @ symbol for timestamps
}
$date_object = new \DateTime( $temp_date );
$unix_seconds = $date_object->getTimestamp();
if( $offset_timezone ){ // add timezone offset
  $my_time_zone = new \DateTimeZone( date_default_timezone_get() );
  $unix_seconds += $my_time_zone->getOffset( $date_object );
}
if( $date_only ){ // remove time from datetime
  $date_object = new \DateTime( '@' . $unix_seconds, new \DateTimeZone( 'UTC' ) );
  $utc_year  = $date_object->format( 'Y' );
  $utc_month = $date_object->format( 'n' );
  $utc_day   = $date_object->format( 'j' );
  $unix_seconds = gmmktime( 0, 0, 0, $utc_month, $utc_day, $utc_year );
}
$hubspot_timestamp = $unix_seconds * 1000; // convert to milliseconds

 

Offsetting the timezone should bring my timestamp value to midnight UTC. But there may be some weird instances where the field in my database still has time information attached, so I use the $date_only check when importing date-only fields (e.g the HubSpot date picker) to make sure hours/minutes/seconds get dropped from the timestamp.

PHP
1 Accepted solution

Accepted Solutions
dennisedson
Solution
Community Manager

@danhammari 

I have a feeling this was a DOH moment! Been there, done that 😅

Thank you for adding this to the forums so others can see!

Thanks,

Dennis




Check out our Community Developer Blog
where we feature our Community driven developer podcast and how to content

View solution in original post

0 Upvotes
1 Reply 1
dennisedson
Solution
Community Manager

@danhammari 

I have a feeling this was a DOH moment! Been there, done that 😅

Thank you for adding this to the forums so others can see!

Thanks,

Dennis




Check out our Community Developer Blog
where we feature our Community driven developer podcast and how to content

View solution in original post

0 Upvotes