473,224 Members | 1,700 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Convert User entered date and time to Unix Timestamp before inserting to database

How do you convert a user inputted date to a unix timestamp before
insterting it into your database? I have a form, with a textfield for
a date that the user inputs in the format mm-dd-yyyy and three dropdow
boxes for hours, minutes, and AM/PM. All of these need to be considered
together and converted to one Unix Timestamp and then inserted to the
MYSQL date field. The type of field is INT (11) so that I can instead
of the standard 0000-00-00 format of MYSQL so that I can do better
calculations and queries. How can this be accomplished?

Thanks.

Jul 17 '05 #1
13 9243
Here's some code to illustrate my question. I changed the dropdown
boxes to input boxes to make it shorter to post, but the concept is the
same...

<?php require_once('Connections/con_date.php'); ?>
<?php
mysql_select_db($database_con_date, $con_date);
$query_rs_date = "SELECT * FROM date_time";
$rs_date = mysql_query($query_rs_date, $con_date) or
die(mysql_error());
$row_rs_date = mysql_fetch_assoc($rs_date);
$totalRows_rs_date = mysql_num_rows($rs_date);
?>

<body>
<form action="" method="post" name="date_form" id="date_form">
<p>Date:
<input name="date" type="text" id="date">
</p>
<p>Hour:

<input name="hour" type="text" id="hour" size="2" maxlength="2">
Minute:
<input name="minute" type="text" id="minute" size="2"
maxlength="2">
<select name="am_pm" id="am_pm">
<option value="0">AM</option>
<option value="1">PM</option>
</select>
</p>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
</body>
</html>
<?php
mysql_free_result($rs_date);
?>

Jul 17 '05 #2
In article <11**********************@c13g2000cwb.googlegroups .com>, perplexed wrote:
How do you convert a user inputted date to a unix timestamp before
insterting it into your database? I have a form, with a textfield for
a date that the user inputs in the format mm-dd-yyyy and three dropdow
boxes for hours, minutes, and AM/PM. All of these need to be considered
together and converted to one Unix Timestamp and then inserted to the
MYSQL date field. The type of field is INT (11) so that I can instead
of the standard 0000-00-00 format of MYSQL so that I can do better
calculations and queries. How can this be accomplished?


here are some never-to-forget functions:

mysql: dateformat, from_unixtime, unixtime
php: strtotime

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>
Jul 17 '05 #3
If you properly validated the date and time fields after user input,
you could then create a PHP timestamp by inserting the proper string
portions of the date into a PHP timestamp function. You would then
have a PHP timestamp for PHP purposes only.

For instance, below, you place the contents of your hour and minute
variable into the function as well as the proper relative string
sections of your date string using the substr function:

mktime($hour,$minute,0,substr($date,0,2),substr($d ate,3,2),substr($date,6,4));

You now have a PHP timestamp, not a MYSQL timestamp.

Note that before you load the PHP timestamp function above, you would
adjust the hour variable for AM/PM in your edit function. For
instance, if your user entered 2AM, you would need to change the
variable to 14 prior to entering it in the PHP timestamp function.

You now have a PHP timestamp for whatever purposes you need it in PHP.
Why couldn't you then just load your database with the standard MYSQL
date timestamp 0000-00-00 00:00:00. You could always pull a range of
dates back out of your database and easily convert them back into PHP
timestamps, etc. or whatever else for complex calculations (PHP which
would be the best place to perform these calculations anyway). Again
using similar string manipulation techniques!

So just format the user input for entry into the database in the
standard timestamp field format 0000-00-00 00:00:00.

You just load a PHP variable as follows:

$temp_date =
substr($date,6,4)."-".substr($date,0,2)."-".substr($date,2)."
".$hour.":"$minute.":00";

and then put this in your database? Remember to have your edit
function put '0's' where they are required by your database! If March,
then the $temp_date would end up having '03' in the month section of
the string, so you have to analyze the user input in some sort of edit
function and translate the input as indicated.

The key to all this is your edit of the user data prior to entering it
in the database.

When you later call your data out of your database by a range of dates,
you could convert the MYSQL timestamps back into PHP timestamps, etc.
fairly easily, again, by manipulating the strings. Sometimes it seems
best to leave the database as just a repository with the proper data
types and to use PHP to manipulate the data once you have called it.
For instance, if you later pulled this out of the database: 2004-01-02
14:06:00 , it would be fairly straightforward getting this string back
into a PHP timestamp -
$variable= "2004-01-02 14:06:00";
$timestamp=mktime(substr($variable,11,2),substr($v ariable,14,2),substr($variable,17,2),substr($varia ble,8,2),subst($variable,5,2),substr($variable,0,4 ));

Apologies if I missed the mark, but I think its best in most situations
to let PHP be the middleman and keep the data standard?

I did a little of this in the past. Let me know if I this helps.
Scott

Jul 17 '05 #4
Scott,

Thank you. This is my first project with intensive date/time functions
in php/mysql. I was not sure which method to use php timestamps and
have a INT type data field or to use a MYSQL timestamp field. Now I
realize that MYSQL timestamp is the way to go. Now I just need to
figure out the best way to get the user to input the correct format,
validate it and then insert it to MYSQL. I will figure out the queries
to sort by date and convert from MYSQL timestamp to PHP timestamp
afterward.

Thanks for the help.
Chris

Jul 17 '05 #5
perplexed wrote:
Scott,

Thank you. This is my first project with intensive date/time functions
in php/mysql. I was not sure which method to use php timestamps and
have a INT type data field or to use a MYSQL timestamp field. Now I
realize that MYSQL timestamp is the way to go. Now I just need to
figure out the best way to get the user to input the correct format,
validate it and then insert it to MYSQL. I will figure out the queries
to sort by date and convert from MYSQL timestamp to PHP timestamp
afterward.

By the way, as far as MySQL column types go, I'd say you need a DATETIME
column and not a TIMESTAMP column: a TIMESTAMP column is automatically
updated by the database whenever the row is updated, and since you were
talking about storing a user-entered date and time that may not be what
you want.

But probably you were already aware of that.

JP

--
Sorry, <de*****@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.
Jul 17 '05 #6
I noticed that Message-ID:
<11**********************@z14g2000cwz.googlegroups .com> from perplexed
contained the following:
Thank you. This is my first project with intensive date/time functions
in php/mysql. I was not sure which method to use php timestamps and
have a INT type data field or to use a MYSQL timestamp field. Now I
realize that MYSQL timestamp is the way to go.


It is? I find UNIX timestamps easier if you don't have to deal with
dates before 1970. The only downside I can see is that you need php
functions to read them ie I can't read a date directly in phpMyadmin for
instance.

It all depends on the app.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #7
.oO(scotty)
So just format the user input for entry into the database in the
standard timestamp field format 0000-00-00 00:00:00.

You just load a PHP variable as follows:

$temp_date =
substr($date,6,4)."-".substr($date,0,2)."-".substr($date,2)."
".$hour.":"$minute.":00";

and then put this in your database? Remember to have your edit
function put '0's' where they are required by your database!
sprintf() might come in handy to add leading zeros where necessary:

$temp_date = sprintf('%u-%02u-%02u %02u:%02u:00', ...);
For instance, if you later pulled this out of the database: 2004-01-02
14:06:00 , it would be fairly straightforward getting this string back
into a PHP timestamp -
$variable= "2004-01-02 14:06:00";
$timestamp=mktime(substr($variable,11,2),substr($ variable,14,2),substr($variable,17,2),substr($vari able,8,2),subst($variable,5,2),substr($variable,0, 4));
Much too complicated, DATE_FORMAT() and UNIX_TIMESTAMP() exist in MySQL.
Apologies if I missed the mark, but I think its best in most situations
to let PHP be the middleman and keep the data standard?


Yep.

Micha
Jul 17 '05 #8
.oO(perplexed)
How do you convert a user inputted date to a unix timestamp before
insterting it into your database?


You don't. If you want to store dates in a database use a native date
type like DATETIME. MySQL offers tons of functions to work with dates.

Micha
Jul 17 '05 #9
.oO(perplexed)
Now I just need to
figure out the best way to get the user to input the correct format,
validate it and then insert it to MYSQL.


1) Split the user submitted string into its values. If you're sure the
format will always be like 'mm-dd-yyyy' you could try it with regular
expressions, sscanf() or explode():

$test = '12-13-2004';
sscanf($test, '%u-%u-%u', $month, $day, $year);

or

$values = explode('-', $test);

2) Validate the date. Use the values from above and checkdate() to see
if the entered date is valid.

3) Validate the time. Check that $hour is between 1 and 12, $minute
between 0 and 59 (don't omit this check even if you use a dropdown box
with just a few values). Then change from 12-hour time to 24-hour time
according to the am/pm setting.

4) Now you have all the values for date and time and can insert them
into the database.

HTH
Micha
Jul 17 '05 #10
>Much too complicated, DATE_FORMAT() and UNIX_TIMESTAMP() exist in
MySQL.

I'm somewhat new to MYSQL, so I agree should use existing functions to
perform the translations where possible, depending upon your form input.

Jul 17 '05 #11
.oO(scotty)
Much too complicated, DATE_FORMAT() and UNIX_TIMESTAMP() exist inMySQL.

I'm somewhat new to MYSQL


OK. ;)
so I agree should use existing functions to
perform the translations where possible, depending upon your form input.


Yep. You can do much more things in a query than just the usual SELECTs,
even complex calculations and manipulations. If the database is able to
do such things and return any data in any particular format you like,
why not use it? Keeps the PHP code smaller.

Micha
Jul 17 '05 #12
Thanks for the tip. There is a lot I'm not aware of. New to MYSQL.
In the recent past I created something that involved dates. I just now
changed the data definition you mentioned and it still works fine. The
field is now a DATETIME field but is being created with the same
TIMESTAMP. So it looks like this functions the same but is now
historical rather than the auto-update that you mention?

I look forward to going through my edit routines and inserting some of
the functions mentioned in these comments, where applicable, to make it
more concise where possible. Although I did not ask the original
question here, I appreciate the comments and hope that some of my
original comments helped as well.

Jul 17 '05 #13
.oO(scotty)
Thanks for the tip. There is a lot I'm not aware of. New to MYSQL.
In the recent past I created something that involved dates. I just now
changed the data definition you mentioned and it still works fine. The
field is now a DATETIME field but is being created with the same
TIMESTAMP. So it looks like this functions the same but is now
historical rather than the auto-update that you mention?


There are still important differences. It's all explained in the manual.

The DATETIME, DATE, and TIMESTAMP Types
http://dev.mysql.com/doc/mysql/en/DATETIME.html

TIMESTAMP Properties Prior to MySQL 4.1
http://dev.mysql.com/doc/mysql/en/TI...P_pre-4.1.html

TIMESTAMP Properties as of MySQL 4.1
http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html

Micha
Jul 17 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ben | last post by:
I would like to use php to query a database and retrieve a unix timestamp. The problem is that mysql is storing the data in the date format and not a timestamp. I am sure that I can amend my...
3
by: Hayden Kirk | last post by:
Ok This is really anoying me... I am using a timestamp colum in my MySQL table, I generate a timestamp with php using time(); When I pull the time from the table I get some date from the...
1
by: Sugapablo | last post by:
How do you convert a unix timestamp to date('Y-m-d H:i:s')?
3
by: Ralf Meuser | last post by:
Hi there, I get form a database time with the format : 'Jan 01 1900 06:00PM' I want to convert it to :HH:MM I tryed it with $heure=(date("H:i",strtotime($row)) but I get allways 20:55. even...
3
by: Shelly | last post by:
Can anyone help? Here is what I have (part, at least) echo $dbResult . ' ' . $dbResult . ' ' . $dbResult . " " . date("M j, Y, g:i a", $dbResult) . '<br>'; Here is what it puts out ...
3
by: Bruno BAGUETTE | last post by:
Hello, I'm looking for a way to convert a unix timestamp to a PostgreSQL date without using ::abstime which seems to be deprecated. Currently, I do that query : levure=> select...
4
by: skulkrinbait | last post by:
Hello How can I convert a date into the Unix timestamp? I don't really mind what format the date is in, something like MMDDYY would be fine, can someone show me what code I would need to do...
3
by: Jonathan | last post by:
I have taken up C++ programming again and need to write a socket client, so far I am able to create the connection to the socket. As I need a unix timestamp in char to put on a socket in a HTTP...
6
by: Geoff Cox | last post by:
Hello, at the moment I can add the combined date and time into MySQL using php $dt1 = date("Y-m-d H:i:s"); is it possible to add the date and time separately? I thought it might be
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.