Transform Date To Mysql Timestamp Via CSV Import
I have a CSV file (from excel) with contains personal data, like names, adress, and birth date. The birth date is in this format yyyy-mm-dd. I would like to import this CSV file into my MYsql database with the "import CSV file" function that is build in PHPmyadmin.
The problem is that I need UNIX timestamps in my database, and not the date in yyy-mm-dd format. Code:
View Complete Forum Thread with Replies
Related Forum Messages:
Transform '07,01,06' Into A Date Format With Just A Query
I've been handed just about the worst HTML/PHP/MySQL site ever scrapped together. While we're in the process of rebuilding the site from scratch for our client, they desperately need some data pulled from their DB. Originally the data was dumped to files but due to attempted injection attacks, the table has grown so large it times out. So I'm trying to pull the data directly but I have a huge problem. I need to pull all data with a date >= July 1, 2006. The problem is the dipsh*t who built this has the date field with a datatype of varchar and the date in the format of གྷ,01,06'. Obviously I can't do a date comparision check with that so how can I transform this data into a correct date format using only a query? I do NOT want to go in and start coding in this site. It's a live site and I'm afaid anything I do will break it. It's MySQL 3.23.57 and all I have access to is PHPMyAdmin. PLEASE help me!!! Someone work a little query magic for me.
View Replies !
Timestamp Import
I need to import into mysql data from DB2. One of the DB2 table columns is of the TIMESTAMP type, which, unlike its MySQL counterpart, supports fractions of a second, up to 6 digits, i.e. it is of the form 2005-07-13-23:45:32.000000 ....whereas the MySQL timestamp type is of the form 2005-07-13 23:45:32 Has anybody done this before? How can I keep the fractions of a second when I do the import
View Replies !
Transform Excel To Mysql
I have a table in excel....wich i want to transform to mysql table...there are some programs that just do that, but i`ve also had a google and read that just saving it as a .csv and then import it to mysql would do the trick... is that true? and my second question, after that....how do i upload the whole mysql table to my website
View Replies !
Date Vs Timestamp
I have a somewhat large table (about 900,000 rows). I regularly need to run many queries against this table and it's is rather slow. Before making any major changes, I wanted to confirm whether the following changes would help. Right now, I run a query like this: SELECT count(*) FROM refer WHERE date >= '$date1' AND date <= '$date2' However, "date" is actually of type timestamp. So I have an index on the field, but it has the same cardinality as the number of rows, as you might expect. I've also heard using a reserved word of 'date' can be a problem, but not sure if that's a performance issue. I think I may occasionally still need this level of granularity for some things, but 99% of the time, I really only need the actual date for my SELECT statements. I'm thinking it might be best to create a field2 that is of type date and use that for the queries, with an index, and drop the index on field1 altogether. The more I think about the more I'm convinced the way I'm doing it now is the reason it is so slow. Can someone please confirm my stupidity?
View Replies !
Timestamp :: Registration Date
As a newbie to MySQL and PHP, I have been able to set up MySQL as test server on my MacOS X and have also succeeded in connecting my test sites registration form to the proper table in MySQL. Everything works fine except getting MySQL to insert the registration date/time automatically as a new record is posted. I have created two "Timestamp"-date columns - the first one for "Change date" and the second for "Registration date". According to what I can gather from reading, this should do the trick, but it doesn't. No date or time is inserted in the registration date field. Can someone explain (in newbie terms) what I am doing wrong and what I should do to make it work? I want the registration date to be filled-in automatically in every new record and then stay unchanged even if other parts of the record are changed.
View Replies !
Date And Unix Timestamp Comparison
i have a field in my database called 'release' that contains a date in the format YYYY-MM-DD, except its stored as text and not in the date format. I need to exclude certain results from my query based on how recent it was released and im having a hard time. ive been trying to use this... SELECT title FROM products WHERE release< FROM_UNIXTIME(unix_timestamp(now()) - 183*86400,'%Y-%m-%d') ;
View Replies !
How To Get Just Date To Display In Field When Using TIMESTAMP?
I would like to capture the date and time in a field for each record. I know that using TIMESTAMP and default value of CURRENT_TIMESTAMP returns the full date and time but I only want the date displayed in my webpage table when displaying the records. Should I be using something else other than TIMESTAMP or is there some parameters I can use to do this?
View Replies !
TIMESTAMP :: Query By Time And Date
I have a field in my table that holds a timestamp. Below is a pseudo-query to describe what I would like to do. SELECT * FROM `mytable` WHERE `saved_date` <= `6/24/05 10:22:34 AM` `saved_date` is the field holding a TIMESTAMP. If anyone could give me the query that would actually do what I am trying to do above,
View Replies !
DATE / TIMESTAMP Default Values
im is having problems with auto inserting date in my table. I dont know what is the default value i will set if column type to DATE. It was succesful though if is use the TIMESTAMP type in the column and is using CURRENT_TIMESTAMP as the dafault value. mysql automatically inserts the current date and time to the database everytime a new record is inserted. The problem with using TIMESTAMP is that i only want the date and not the additional time. Is there a default value I can use for the DATE function instead of using TIMESTAMP? I already tried NOW(), CURDATE(), CURRENT_DATE() to no avail
View Replies !
TIMESTAMP :: Creation And Update Date
I want to create an auto creation date and update date in my mysql table. I am using the "MySQL Cookbook" approach, using the TIMESTAMP field which basically is: - create 2 new fields in the table (t_create and t_update) - both NULL - when a new record is created they both will register a common timestamp - when the record is modified the t_update will change, but not the t_create
View Replies !
Import Error With A Date Field
ERROR 1067 (42000) at line 13: Invalid default value for 'date' Here is the line it is referring to: create table Purchases ( purchasesID int(10) not null auto_increment, model varchar(13) , date timestamp default 'CURRENT_TIMESTAMP' , quantity int(10) , customerID int(10) default '0' , PRIMARY KEY (purchasesID), KEY model (model), KEY date (date) );
View Replies !
Format Date And Decimals On Import
I will import on a regular basis a text file from PayPal Europe into a MySQL Db. Dates are provided in this format: DD/MM/YYYY Decimals in this format: XXX,XX (comma before decimal instead of period) How can I write an insert query that will automatically format the date to YYYY-MM-DD and the decimals to XXX.XX? I tried this for the date: INSERT INTO paypal_eu( date_transac ) VALUES ( STR_TO_DATE( '14/06/2007', '%Y-%m-%d' ) )); but it doesn't work. How about the decimals? I'm using MySQL 3.23.58
View Replies !
Format Date And Decimal On Import
I will import on a regular basis a text file from PayPal Europe into a MySQL Db. Dates are provided in this format: DD/MM/YYYY Decimals in this format: XXX,XX (comma before decimal instead of period) How can I write an insert query that will automatically format the date to YYYY-MM-DD and the decimals to XXX.XX? I tried this for the date:
View Replies !
Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form: year | season | hits 2004 | Summer | 42 2004 | Autumn | 43 2005 | Spring | 51 2005 | Summer | 52 2005 | Autumn | 53 2006 | Spring | 61 I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form year | Spring | Summer | Autumn 2004 | ------ | ----42 | ----43 2005 | ----51 | ----52 | ----53 2006 | ----61 | ------ | ------ Could anyone suggest a mysql query to generate the required output?
View Replies !
Extract, Transform
We have an old legacy database at my company called CIMPRO, running on a SCO UNIX box. We're trying to find a way to connect to the CIMPRO database, grab the data, and send it to a Mysql database. I've been able to successfully connect to the database using PHP and ODBC to view the data in the tables. i'd like to find a way to run a Linux tool or script to connect to the database, do an initial copy of the table structure from the legacy database to Mysql (on the linux box) and then copy selected data. If that is doable I'd also like to be able to refresh the table data periodically (e.g., cron job) either by deleting and reimporting or by checking to see what has changed.
View Replies !
Transform Rows To Columns
I have a dataset that looks like this | profileID | groupID | Value | | 3 | 2 | 26 | | 3 | 3 | 85 | | 3 | 4 | 40 | | 3 | 5 | 35 | | 4 | 2 | 27 | | 4 | 4 | 81 | | 4 | 5 | 76 | | 5 | 2 | 48 | | 5 | 3 | 50 | | 5 | 4 | 59 | etc i want to transform it into columns (denormalize) for a specific business purpose, so that it looks like this; profileID Group2 Group3 Group4 Group5 3 26 85 40 25 4 27 NULL 81 76 5 48 50 59 NULL etc
View Replies !
MySQL Timestamp And PHP
I'm using MySQL in-built timestamp of 14 characters e.g., 20060713174545. Using PHP date() function, is it possible to get correct date out of it. Does MySQL timestamp depends on Linux/ Windows machines? Or I've to use PHP to build dates and manually insert into database.
View Replies !
Passing Date Via Form For Mysql Date Type
I've created a database for tracking our paper inventory. Basically when an individual takes paper, or envelopes the quantity is entered into the database, along with some other items. This all works great. I also have two fields that use the "Date" and "Time" types for holding the date and time of the initial transaction. I've created a seperate php script that we will use for our "end of month" reports. The script goes through and adds up the cost of each "purchase" between a specified time frame (1 may thru 31 may for example). This script works for me as long as I perform my query with my condition formated as such:
View Replies !
MySQL Compare A Timestamp
I would like to compare a timestampfield with an offset of days, which is specified in the database for each dataset an the current date. For example: timestamp 20040403120000 offset 7 so i would like to find all datasets which are before 20040411120000.
View Replies !
Timestamp - Only When Created Mysql
How can i add a timestamp that is only updated when the record is inserted, not when the record is updated. A kind of "date created" stamp. Can this be done by setting the field up in phpmyadmin - ie not in the script im using (php)
View Replies !
MySQL Generate UNIX Timestamp
Cam MySQL create a UNIX timestamp during an INSERT? I want to create a timestamp that is the number of seconds since January 1st 1970. Are there any inbuilt functions for doing so?
View Replies !
Can't Insert A Unix Timestamp Into Mysql
Just upgraded to 4.1.8 and when I create a field with a date value (field is trdate), it always has default of 0000-00-00. And when I insert values with unix timestamp values (which I prefer, and they come from php's strtotime function),the record does not insert properly. I tried to set, through phpmyadmin, unix/current timestamp and to no avail. If I use a strtotime php function to convert a date into a timestamp, what is the proven way to make sure that value is saved during inserts? I am on the mysql manual, searched this board, but nothing works.
View Replies !
Timestamp And Current_date/timestamp
Im stuck with a sql query. Basically I have a db that stored a timestamp off everyone who has a successful login. I want to be able to count all the people who have logged into today? How do I do that? I thought the following would work but it didn't: SELECT count(date) FROM nn_users_logs WHERE date = CURREN_TIMESTAMP
View Replies !
View The Timestamp In MySQL As The UNIX By Adding 0 To The End Of The Row
i've read that in MySQL 4.1, you can view the timestamp in MySQL as the UNIX by adding 0 to the end of the row. Can someone help me modify my code so that this same idea will also in my code? <tr><td colspan="2"> <?php $datetime=$row["time" + 0]; $year = substr( $datetime, 0,4 ); $mon = substr( $datetime, 5,2 ); $day = substr( $datetime, 7,2 ); $hour = substr( $datetime, 9,2 ); $min = substr( $datetime, 11,2 ); $sec = substr( $datetime, 13,2); $orgdate = date("l F dS, Y h:i A", mktime( $hour, $min, $sec, $mon, $day, $year)); ?> <b>Date:</b> <?php echo $datetime; ?></td></tr>
View Replies !
INSERT Non-MySQL Date Into MySQL Date Field
I have a non-MySQL database that has a date value that is an integer value (4 byte unsigned integer representing the number of days since 1904, ie 01/01/2000 = 35063) Do I need to re-format this to use in an SQL to INSERT it into a MySQL table? If I do need to reformat it, what is the best format to use.
View Replies !
SQL Syntax "Transform"
I have this table in the DB MySQL: Code: key | date -------------- AAA 2006-04-03 AAA 2006-04-03 AAA 2006-04-04 AAA 2006-04-04 AAA 2006-04-05 AAA 2006-04-05 AAA 2006-04-05 BBB 2006-04-03 BBB 2006-04-03 BBB 2006-04-04 BBB 2006-04-04 BBB 2006-04-05 BBB 2006-04-05 BBB 2006-04-05 CCC 2006-04-03 CCC 2006-04-03 CCC 2006-04-04 CCC 2006-04-04 CCC 2006-04-05 CCC 2006-04-05 CCC 2006-04-05 I need this: Code: key 2006-04-03 2006-04-04 2006-04-05 ------------------------------------ AAA 2 2 3 BBB 2 2 3 CCC 2 2 3 Whit database MS Access I use TRANSFORM syntax but this syntax not working whit database MySQL...
View Replies !
Changing The Default Time Zone Of A Current Timestamp In MySQL
I have a shared host which uses the time zone MST (GMT-7), and I cannot seem to change the time zone for my own database. What is the best way to create a timestamp field in a table that has the default value be the current time in GMT-0? Is setting the default value of the timestamp field to "DATE_SUB(NOW(), INTERVAL -7 HOUR)"
View Replies !
Import XML Into MySQL
Sorry. I missed this discussion previously. Now I need the answer. Figures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw
View Replies !
MySQL Import CSV
I've been going nuts trying to import a CSV that has dates. I've tried everything I can think of. Below are the formats I've tried. "Data 1", "Data 2", 12/26/2005, ..... "Data 1", "Data 2", "2005/12/26", ..... "Data 1", "Data 2", "2005-12-26", ..... How do you do it
View Replies !
Mysql Import
when should I use mysqlimport, or load data and when should I write my own Perl parser to help load the table? What criteria would be needed to decide to read a file into a hash?
View Replies !
Import Into MySQL
I need to be able to automate the import of a .sql or .csv file into my MySQL database. I am currently having a file dropped on my server from a distribution program externally every 15 minutes, so preferably I would like to import the file every 15 minutes also. Is this possible using a cron job? Or using Perl or PHP?
View Replies !
Import Dump From Unix Mysql To Windows Mysql
I am trying to load a dump from a MySQL database on my ISP's server to my development environment on a Win 2K machine (running MySQL). When I run the mysql command to run the load script, MySQL gives the following complaint: "ERROR 1064 at line 21: You have an error in your SQL syntax. Check the manual t hat corresponds to your MySQL server version for the right syntax to use near 'D EFAULT CHARSET=latin1' at line 7" The dump from the ISP's server is created using mysqldump. The ISP is running MySQL 4.1.20. My development environment is MySQL 4.0.26. I have no problem loading dumps made from my development environment usng phpmyadmin.
View Replies !
MySQL Export/import
I have installed MySQL on my Linux Mandrake 10 workstation; I didn't change any default values (except for the user-accounts). I used a CMS to create a website which I want to upload to my hosting provider. When I export my database (using PHPMyAdmin) from my workstation to a local file and then on the server (again using PHPMyAdmin) I import the database from the local file, some characters are 'fucked up'. My local install is MySQL 4.1.2-alpha-standard (from Mandrake RPM) and it seems that the default character set is utf8 with collation utf8_general_ci. But some columns in my tables (even within other databases) use collation latin1_swedish_ci (strange..). The server install is MySQL 4.0.15. Using mysqladmin with the 'variables' command, I found out that variable 'character_set' has value 'latin1' and the variable 'character_sets' has value: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 The 'convert_character_set' variable seems to have no value. I tried creating a database on the server using mysqladmin with the --default-character-set=utf8, but that also doesn't help. (it doesn't give any error message, but utf8 is not listed in the 'character_sets' variable).
View Replies !
Import Of Database In Mysql
I get this error when I am importing the database. What am I doing wrong? ERROR 1064 at line 625735: You have an error in your SQL syntax near ' begin_excerpt varchar(250) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM' at line 5
View Replies !
Import Users From Old Mysql Db?
I'm trying to import all of the info in the mysql database (ya know the db that keeps track of all the users and what they can and cannot do). I tried just a standard mysqldump from the old box. Copy the file over to the new box. And do a mysql -uroot -p < /tmp/mysqldb.sql. Lo and behold it doesn't work. Looks like there are more columns in some of the tables.
View Replies !
Import From Excel To MySQL
I have multiple spreadsheets in Excel format and in .csv format too, that I would like to possibly import to a mysql database. All of the excel and csv files have is one huge colum and they are only 1 cell on each line. An estimate of 7k-8k domain names I need to run a whois on. Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name.
View Replies !
Mysql Import CSV Query
I am using the following command to import a csv file into my MySQL database but for some reason the first column in the CSV file is being ignored. Any thoughts as to why this might be happening? Asked this in the Win32 forum before realising that might be the wrong place to ask... /usr/bin/mysqlimport -d --fields-terminated-by=, --user=root -p databasename solicitors.csv
View Replies !
Import Data Into Mysql
is there a way around it, other than scrubbing the data ? currently, the dates are in mm/dd/yyyy format if i want to use in import file with fields that have this format, is there any way of applying a conversion to that field on the fly ?
View Replies !
MySQL Import On WAMP
I installed WAMP on XP to use a test server and can't figure out this silly windoze command line thing for MySQL. It seems my linux commands are Klingon to Windows. I try this.. mysql> username -p database < c:wampwwwfile.sql and I get a bunch of syntax errors. I tried dual slash and everything and it's not working. The file is 80MB so it has to be done at the command prompt. Is there anyone out there? Is there anyone listening? I've searched low and hi and there doesn't seem to be a page with how to do this. I've seen LOAD but that's just for a table.
View Replies !
|
TRACKER
