CODEWITHSUNDEEP
mysql
Boky
Boky

Reputation: 12064

Getting datetime field from a database

I have one rare problem. I use this query to extract a date and time 

$datum_query = mysqli_query($kon, "SELECT EXTRACT(DAY FROM datetime) AS dan, EXTRACT(MONTH FROM datetime) AS mjesec,
    EXTRACT(YEAR FROM datetime) AS godina, EXTRACT(HOUR FROM datetime) AS sat, EXTRACT(MINUTE FROM datetime) AS minute, EXTRACT(SECOND FROM datetime) AS sekunde FROM cutoffs WHERE id=". $redCutoff["cutoff_id"] ."");
    $datum = mysqli_fetch_assoc($datum_query);

    if($datum["mjesec"] == 1){
        $datumLevering = $datum["dan"] . ". Jan " . $datum["godina"];
    }elseif($datum["mjesec"] == 2){
        $datumLevering = $datum["dan"] . ". Feb " . $datum["godina"];
    }elseif($datum["mjesec"] == 3){
        $datumLevering = $datum["dan"] . ". Ma " . $datum["godina"];
    }elseif($datum["mjesec"] == 4){
        $datumLevering = $datum["dan"] . ". Apr " . $datum["godina"];
    }elseif($datum["mjesec"] == 5){
        $datumLevering = $datum["dan"] . ". Mei " . $datum["godina"];
    }elseif($datum["mjesec"] == 6){
        $datumLevering = $datum["dan"] . ". Jun " . $datum["godina"];
    }elseif($datum["mjesec"] == 7){
        $datumLevering = $datum["dan"] . ". Jul " . $datum["godina"];
    }elseif($datum["mjesec"] == 8){
        $datumLevering = $datum["dan"] . ". Aug " . $datum["godina"];
    }elseif($datum["mjesec"] == 9){
        $datumLevering = $datum["dan"] . ". Sep " . $datum["godina"];
    }elseif($datum["mjesec"] == 10){
        $datumLevering = $datum["dan"] . ". Okt " . $datum["godina"];
    }elseif($datum["mjesec"] == 11){
        $datumLevering = $datum["dan"] . ". Nov " . $datum["godina"];
    }else{
        $datumLevering = $datum["dan"] . ". Dec " . $datum["godina"];
    }
    $leveringVrijeme = $datum["sat"] . "u" . $datum["minute"];
    $leveringDateandTime =  $datumLevering . "-" . $leveringVrijeme;

I store in my database a datetime field and when i want to get result if value of the field is for example 2015-08-19 20:10:00, i get good result in $leveringVrijeme (20u10), but if the value is for example 2015-08-19 20:00:00, then i get in $leveringVrijeme (20u0).

Why i do not get the other 0?

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

extract returns the values as numbers. For your formatting purposes, you seem to want zero-padded strings. So, instead of extract, use date_format():

select date_format(datetime, '%d') as day,
       date_format(datetime, '%m') as mon,
       date_format(datetime, '%Y') as year,
       date_format(datetime, '%H') as hours,
       date_format(datetime, '%i') as minutes,
       date_format(datetime, '%s') as seconds

This retrieves them as zero-padded strings.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

You can consider casting it to string literal or VARCHAR using CAST like

SELECT EXTRACT(DAY FROM datetime) AS dan, 
EXTRACT(MONTH FROM datetime) AS mjesec,
    EXTRACT(YEAR FROM datetime) AS godina, 
    CAST(EXTRACT(HOUR FROM datetime) AS VARCHAR) AS sat, 
    CAST(EXTRACT(MINUTE FROM datetime) AS VARCHAR) AS minute, 
    CAST(EXTRACT(SECOND FROM datetime) AS VARCHAR) AS sekunde 
    FROM cutoffs 
    WHERE id=". $redCutoff["cutoff_id"] ."");

Upvotes: 1

Related Questions