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

danhammari · ‎Nov 29, 2021

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.