UNIX Timestamp

apek · January 18th, 2004, 09:11 AM

quasedilla5 once said:

Quote:

quote:

Quote:

If you want a custom time, I would suggest changing the field type to CHAR (a fixed character length) And use PHP to generate a timestamp, I would suggest using UNIX timestamps which are a bit more compatible with the PHP functions mktime() and date() and just IMO more flexible overall. date() will take a UNIX timestamp and format it however you like.

More information:
http://www.php.net/date
http://www.php.net/mktime
http://www.mysql.com/doc/en/DATETIME.html

my date field is in char datatype...
i dont understand what is UNIX timestamp and what is advantages...
if i want to manipulate my date like counting different between days,use BETWEEN in query,how to do that if my datatype is in CHAR???

richard.york · January 18th, 2004, 03:16 PM

Hi Apek,

This is what a UNIX timestamp looks like:
1074402000

(Today)

Actually if you are going to use a UNIX timestamp I would use an INT field type... I don't know what tangent I was off on when I suggested CHAR field type.

With an INT field type you can make queries like this:

SELECT * FROM event WHERE time < 1074402000 AND time > 1072933200;

Basically it says get all the records between these times Less than Jan 18 and Greater than Jan 1.

MySQL also has a function to insert UNIX timestamps and that is:
UNIX_TIMESTAMP()

http://www.mysql.com/doc/en/Date_and...functions.html

The PHP manual pages that I provided elaborate on how you may format a UNIX timestamp to your liking with the date() function. And how you may create a UNIX timestamp with the mktime() function, and as such I won't elaborate more on that.

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

nikolai · January 19th, 2004, 02:59 PM

A shorter answer: A UNIX timestamp is an integer that holds the number of seconds that have elapsed since the epoch*. The UNIX epoch is midnight (00:00:00am) January 1, 1970 GMT.

* http://foldoc.doc.ic.ac.uk/foldoc/fo...gi?query=epoch

Take care,

Nik
http://www.bigaction.org/

apek · January 20th, 2004, 01:50 AM

i want to convert date entered to time stamp format...

Code:

$test=create_timestamp($_POST["tarikhMasukan"]);

then in custom function create_timestamp

Code:

function create_timestamp()
{

  $timestamp = date("HismdY");

  return $timestamp;

}

and then the return result will be used in my INSERT INTO query:

Code:

$sqlquery = query("INSERT INTO preventiveMaint(tarikhMasukan,noTag,idPekerja,waktuMula,masaMula,waktuTamat,masaTamat,hariBD,jamBD,minitBD,jenisOperasi,komponen,kuantiti,kos,maklumatKerja,jenis) VALUES('$test', '{$_POST["noTag"]}', '{$_POST["idPekerja"]}', '{$_POST["mulaServis"]}', '{$_POST["masaMula"]}', '{$_POST["tamatServis"]}', '{$_POST["masaTamat"]}', '{$_POST["hariBD"]}', '{$_POST["jamBD"]}', '{$_POST["minitBD"]}', '{$_POST["jenisOperasi"]}', '{$_POST["komponen"]}', '{$_POST["kuantiti"]}', '{$_POST["kos"]}', '{$_POST["kerja"]}','{$jenis}')");

but it is weird why in mysql database still showing
00000000000000???

nikolai · January 20th, 2004, 02:05 PM

What exactly is the data type of your column? You said it's a char type, but you didn't specify the length.

You're confusing a few things with your code. You pass a parameter to create_timestamp() but you don't use it. That means that that you're always creating a date/time string for the exact time that the function is called. Also, you're not creating a timestamp in that function, you're creating a date string. The format of this string is also pretty strange -- you don't have any separators in the string to distinguish all the different parts.

For example, if I were to run your function RIGHT NOW (Jan 20, 2004 10:04:00 am), your create_timestamp() function would return this string:
"10040001202004"

There are a couple things you need to notice about this value. First, it's 14 characters long. 2nd, if it's converted to an integer, it becomes much larger than the largest 4-byte integer can store.

If you're dealing with dates and/or times in MySQL, you should use either an unsigned integer (to hold a unix timestamp), or a DATE, TIMESTAMP, or DATETIME field type, depending on what you're storing.

Then, you'd want to rewrite your create_timestamp() function to translate the incoming parameter to the format the database expects.

Take care,

Nik
http://www.bigaction.org/