How do I convert from 5 digit number date in PHP?

Question

I tried to import an Excel file in the database,but the dates are shown as 5 digit numbers 44605 representing 2/13/2022.What can I use to convert the numbers when trying to import the tables from from the file into the database?The columns for date are data_factura and data_scadenta.

<?php
include("../db/sql/MysqliDb.php");
include('../vendor/autoload.php');
include("../db_conn.php");
  
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

  if(isset($_FILES["filename"]))
{
  
 $file = $_FILES["filename"]["tmp_name"];

 $file_open = fopen($file,"r");

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($file);

$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5


// for ($row = 1; $row <= $highestRow; ++$row) {
//     echo '<tr>' . PHP_EOL;
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $value = $worksheet->getCellByColumnAndRow($col, 1)->getValue();
        if($value == "Cod client")
                        $column1 = $col;
        if($value == "Client")
                        $column2 = $col;
        if($value == "Adresa/sediul social")
                        $column3 = $col;
        if($value == "Data Factura")
                        $column4 = $col;
        if($value == "Scadenta")
                        $column5 = $col;
        if($value == "Total valoare")
                        $column6 = $col;
        if($value == "Total TVA")
                        $column7 = $col;
        if($value == "Total factura curenta cu TVA")
                        $column8 = $col;                                 
    }
 
// echo $column2;

for ($row = 2; $row <= $highestRow; ++$row) {
   
    $cod_client = $worksheet->getCellByColumnAndRow($column1, $row)->getValue();
    $client= $worksheet->getCellByColumnAndRow($column2, $row)->getValue();
    $adresa = $worksheet->getCellByColumnAndRow($column3, $row)->getValue();
    $data_factura = $worksheet->getCellByColumnAndRow($column4, $row)->getValue();
    $data_scadenta = $worksheet->getCellByColumnAndRow($column5, $row)->getValue();
    $total_valoare = $worksheet->getCellByColumnAndRow($column6, $row)->getValue();
    $total_TVA = $worksheet->getCellByColumnAndRow($column7, $row)->getValue();
    $total_factura_curenta = $worksheet->getCellByColumnAndRow($column8, $row)->getValue();

    // echo $cod_client.', ';
    $adresamodificata= str_replace("'", "",$adresa);

$db->rawQuery("INSERT INTO cod_facturi(cod_client,client,adresa,data_factura,data_scadenta,total_valoare,total_TVA,total_factura_curenta) VALUES ('$cod_client','$client','$adresamodificata','$data_factura','$data_scadenta','$total_valoare','$total_TVA','$total_factura_curenta')");

}

This is clearly a Excel problem.. you shouldn't use Excel as exchange format due to it's sometimes "weird" behaviour, eg. formatting values differently — Honk der Hase, Apr 29 '22 at 11:36
"Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900." )see: [DATEVALUE](https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252#:~:text=Remarks,date%20values%20to%20serial%20numbers.)) and 44605 happens to be the `=DATEVALUE("2022-02-13")` — Luuk, Apr 29 '22 at 11:39
What have you tried sof ar? Where are you stuck? How is this related to SQL? Also, be warned that the given `INSERT` query is highly vulnerable for SQL injection. Please have a look at prepared statements to avoid getting hacked — Nico Haase, Apr 29 '22 at 12:03
When you enter that value on https://www.unixtimestamp.com/, is shows February 13th 2022 again — Luuk, Apr 29 '22 at 12:12

score 0 · Answer 1 · answered Apr 29 '22 at 11:56

0

In PHP we convert timestamp to a date by using date() method that accepts date format and the timestamp in seconds.

 date(string dateformat,int timestamp);

 date('m/d/Y H:i:s', time());

answered Apr 29 '22 at 11:56

Hamza Sehouli

166
5

It works now,I converted again from the timestamp 1644710400 with this function. – Madalinaxyz Apr 29 '22 at 12:16

How do I convert from 5 digit number date in PHP?

1 Answers1