Comparing Dates If One Is Unix Timestamp
I'm building a simple cms and i'm trying to get something done.. I store the dates as UNIX timestamps. However i want to add a feature let's say to display how many entries have been posted today. Something like [Articles posted today: 3]
View Complete Forum Thread with Replies
Related Forum Messages:
Which Is Faster In MySQL? Comparing Unix Times, Or DATETIMES?
I'm leaning towards using unix times inserted into my MySQL for ease of use in manipulating the data with PHP. I think this will be the final decision, which one would be faster? -- Mysql: * INT (4 bytes) * DATETIME (8 bytes) * TIMESTAMP (4 bytes) -- My PHP application does alot of querying from the database to compare specific data within TIMERANGES, so always, I'm querying with "time >" or "time <". My question is would doing the WHERE time query with the native MySQL DATETIME be faster, than doing a WHERE query with the unix timestamp?
View Replies !
Using CURDATE With UNIX Timestamp
I have a table where the dates are stored in UNIX timestamp format. I'm trying to use the CURDATE term for a select query but it isn't working. Does CURDATE work with the UNIX time stamp? Any help is appreciated. here's the query: select first_name, last_name, date_expires, title from cl_member, `user`, cl_chapter where cl_member.user_id=`user`.user_id and cl_member.chapter_id=cl_chapter.chapter_id and cl_member.chapter_id=14 and month(date_expires)=month(CURDATE()) ORDER BY last_name
View Replies !
Covert Unix Timestamp
Been trying to use DATE_FORMAT on a timestamp in my table. However, just noticed that it's a UNIX timestamp, and not a datetime() timestamp. Is there a function that convert unix timestamps in a sql statement? Trying to get the format "dd/mm/yyyy"
View Replies !
Unix Timestamp Select Query
I am having trouble with the following sql query.I am trying to select all records in the future where the date is greater than today but to include today, the date field in the db is an unix timestamp. Code: "SELECT * FROM mysqltable WHERE mysqldatefield > now()-1 ORDER BY mysqldatefield DESC"
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 !
Inserting Current Unix Timestamp
I am working on coding a query to generate a phpBB useraccount in the mySQL database. INSERT INTO kylebt_Forums.phpbb_users SET `username` = '%username%', `user_password`=MD5('%password%'), `user_email`='%email%', `user_regdate`='' The user_regdate field is giving me problems as far as accepting the 10-digit unix_timestamp(); Apparently phpBB reads the PHP command time() however I am not sure how I need to implement this in the above code to have the timestamp show up correctly in the mySQL table. Currently, the user_regdate is showing up as the default value of '0'
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 !
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 !
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 !
Comparing Timestamp To D/m/yyyy
I have one table, (table1), that contains a timestamp and another table, (table2) that contains the date in this format: d/m/yyyy. I need to update table1 based on data in table2 with one of the clauses being the dates match. So something like this: UPDATE table1 SET x = y WHERE table1.date = table2.date But I"m not sure how to compare a timestamp to that date format?
View Replies !
Comparing Month And Year From A Timestamp
select all artices where the month of the timestamp = $given_month AND year of the timestamp = $given_year. To accomplish the above I tried (note: blog_date is the timestamp in the db): SELECT * FROM blog_entries WHERE MONTH(blog_date) = 'january' AND YEAR(blog_date) = 2009. The error I'm getting is unknown column MONTH(blog_date), etc....
View Replies !
Comparing Dates?
i have an 8 digit date string that i want to compare to a column of type 'date' in a MySQL db. the 8 digit string is in the format 'CCYYMMDD'. the question is: do i need to put it into the 'CCYY-MM-DD' format in order to get correct output from the datediff function, or can i simply leave it how it is?
View Replies !
Comparing Two Dates?
I am trying to compare dates in MySQL, but one of my dates from the curdate()-1 method, returns a string that looks like this 20081210 curdate() method returns '2008-12-11' Both are not dates, so they will not compare. Here is my query: SELECT Order_Number FROM <TableName> WHERE date(Order_Date) BETWEEN curdate() AND curdate() -7;
View Replies !
Comparing Dates
I have a problem with me. I am describing the whole situation. I have a table, the structure of the table is as follows: Table Name: Announcement FieldName Data Type(Size) Announcement Varchar(50) St_Hr Decimal(10,0) St_Min Decimal(10,0) St_Day Decimal(10,0) St_Mon Decimal(10,0) St_Year Decimal(10,0) End_Hr Decimal(10,0) End_Min Decimal(10,0) End_Day Decimal(10,0) End_Mon Decimal(10,0) End_Year Decimal(10,0) The data stored is: Test Announcement,10,20,6,10,2005,11,0,8,10,2005 Now, I have to write a query to retrive announcements after filtering them as per the mentioned Starting and Ending Date & Time. The condition should use the local date and time.
View Replies !
Convert Existing Dates To UNIX Timestamps
I have a DB of roughly 700 records, growing by about 100 a day. The first 600 odd records have a field called issue_submitted_date which is in the format Month Name DayNumber, Year Number, Hour:Minute AM/PM, like August 5, 2004, 5:00 pm That was a bad idea... So now I store issue_submitted_date as a UNIX timestamp. I'm trying to come up with a query to update the other 600 records as unix timestamps as well, but I cant find any way to convert the way I have stored the month name and such, only when the record has been stored with all numeric types (such as 2004-01-01 21:22:00). update issues set issue_submitted_date = unix_timestamp( "formatting options", issue_submitted_date ); I was looking at GET_FORMAT and MONTHNAME, but these don't seem to be capable of doing what I'm trying to do. Can anyone give me some insight or assistance building this query?
View Replies !
Comparing Dates And Choosing
if( date_add(cur_date(), interval 30 day)>='$thisDate','$thisDate',date_add(cur_date(),interval 30 day) So: if the current date plus 30 days is larger then thisDate, use thisDate else use the current date plus 30 days.
View Replies !
Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates now how do i query my database for example ive got 2 variables $startdate = 2006-1-1 00:00:01 $enddate = 2006-1-1 23:59:59 these represent the 1st and last second of the first of january 2006 i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry how do i get all the entries between the start time and the end time.. can i use less than < and more than > as these are not really integar values the column type is datetime but there is also another column of type datetime
View Replies !
Comparing Dates Stored In The Format Yyyy-mm-dd
Hello, I have a date field in a mysql database, that is stored in the format yyyy-mm-dd. I would like to subtract 10 days from the date and then compare it to the current day, so say I select a date in the db is 2008-09-15, I want to compare today (2008-07-28) to 2008-09-05. How would I do this? (I'm using php).
View Replies !
Comparing String Dates To Current Date For Month And Day
Trying to set the display column to a no if the ending date is before or at the current date. Comparing string dates to the current date for month and day only. Am using CONCAT because the ending date needs to be figured into a date as month_end is in a varchar type and day_end is a tinyint type. The query does run, but it affects everything where continuous != 'always' - seeming to ignore the AND part. UPDATE Listings SET display = 'no' WHERE continuous != 'always' AND STR_TO_DATE(CONCAT(month_end, '-', day_end), '%M-%e') <= DATE_FORMAT(CURDATE(), '%M-%e') What should be done differently here?
View Replies !
Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like: 2/2/2006 0:00:00, 12/20/2006 9:22:05 Any ideas how to format those into mysql friendly dates? Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula? =========================================== LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt' INTO TABLE auto (field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9) SET id = MD5(UUID()) FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '
View Replies !
Update String Dates Into Mysql Dates
I've got a table that someone created with varchar fields for month, day, and year. I've added a DATE field to the table, and now I want to combine all three of these strings into one DATE and stick it into the DATE field. Once I've got this done, I can delete those three varchar fields and just have the nice DATE field. Here's the command I've tried in several variations but no luck--syntax errors every time: UPDATE SET OCR_Entry_Date = date_format(str_to_date(concat_ws('-', OCR_MM, OCR_DD , OCR_YYYY) ,"%b-%d-%Y"), "%m-%d-%Y") WHERE OCR_YYYY = '1974' OCR_Entry_Date is a DATE field and the others are the varchar fields. My logic is basically: 1. concatenate the separate field values into a single string 2. convert the string into a date 3. format the date in the way I'd like it in the table
View Replies !
MSSQL Dates -> MySQL Dates Automatically?
We're using data feeds that were originally meant for MS SQL and the dates in the data feed (tab delimited text files) are formated like: Oct 21 2007 In MySQL, the date fields are formatted as datetime fields and when we do an import the dates all come through as 0000-00-00 00:00:00. We're importing using the MySQL "LOAD DATA LOCAL INFILE" command and just dumping the text files right into freshly truncated tables. Is there a way when loading the files to find and replace the dates maybe? Can MySQL convert the dates? I have a shell script downloading an archive and uncompressing, then it runs a PHP script that loops through the files runningt he LOAD DATA command. Any advice would be appreciated... I'm just looking for the easiest (and least server-intensive) way to get the date issue fixed. We've already tried begging the vendor but they're not yet ready to start supporting other date formats (even though their MS SQL db can output a date format friendly to MySQL). Thanks!
View Replies !
List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.
View Replies !
Dates Pre 1970 And Dates After 2050
i have MySQL version: 4.1.16-nt, when i try to insert a date pre 1970 or after 2050 into a timestamp field, the date is stored as all 0's, how can i save a date pre 1970 and after 2050 into a mysql field?
View Replies !
List All The Dates Between 2 Given Dates Using SELECT
how I can list all the dates between 2 dates (inclusive) using SELECT sql statement in mysql? I have a member table with the following columns: id int autoincrement name varchar(40) join_date date I want the following output (date, count of members joined on that date): 1 April 2009...... (2 members joined) 2 April 2009...... (0 members joined) 3 April 2009...... (3 members joined) .....so on ...upto 30 April 2009.....(1 members joined) For the above output I need the SQL command that is valid in mysql. Is there anyone who can produce the above result with a single SELECT command. Please don't suggest that is possible using php for loop or creating a mysqlorary table first and then inserting all the dates into it. Then make a join between mysql table and members table using date as GROUP by column etc. etc. I know this is possible using some user variables as I searched through the various SQL tutorial sites. But I am unable to figure out how I can produce a list of all dates between 2 given dates.
View Replies !
Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime. I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !
View Replies !
How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.
View Replies !
Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS. What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm
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 !
Using TCP/IP Instead Of Unix Sockets
An associate has asked me to take a look a problem he's having with starting mysql. I thought I'd give it a shot to learn some more about mySQL before having him turn it over to someone more knowledgeable than I. But I have to ask here first. Attempting to connect to the database via mysqladmin or mysql results in the error (paraphrased) "Cannot connect to local database using socket "/var/lib/mysql/mysql.sock". That is the path to the socked specifiet in both mysqld and client sections of the my.cnf. mysqladmin -h `hostname` version shows that the Connection is via TCP/IP rather than Unix Sockets. Running on Redhat Linux AE 3 (I think) /var/lib/mysql is world-writable-readable, but it is not owned by user mysql (my next attempt to fix this) /var/lib/mysql/mysql.sock is 777. My question: What would be causing mysqld to be using TCP rather than Unix sockets? I've checked permissions, verified settings in my.cnf (which is very sparse... other entries are for logging), confirmed mysqld is running and accessible via TCP/IP.
View Replies !
Unix Date Problems
1) I was investigating the unix_timestamp routine in mysql (version 3.23.46-nt) and for some reason the unix epoch (1-1-1970) was returned with a value of -3600. Then, as you can see, it suddenly corrected itself. I can't replicate this error, but it caused me to shudder. Attached is the output from the command line client. 2) The function from_unixtime(n) wraps on my server after 2^31 seconds. e.g. select from_unixtime(2147483648) returns "1900-01-00 00:00:00". I suspect this is caused by the underlying time_t size that mysql was compiled with. Is this so? Are any versions of mysql compiled with a larger time_t? +---------------------------+ | from_unixtime(2147483648) | +---------------------------+ | 1900-01-00 00:00:00 | +---------------------------+ 1 row in set (0.00 sec) mysql> select unix_timestamp(); +------------------+ | u................................
View Replies !
Unix Socket Vs TCP Mode
Our mysql database always run smoothness, but there have a strange question always happy. We retrieve content using unix socket mode is always Correct, using TCP mode sometime will meet no return Error. What's wrong with TCP mode? For example, We need to retrieve content from table Tab_Memo, the condition with ‘large’ is over 20KB, with ‘small’ is about ‘5KB’. Using unix socket mode: ---------------------------------- mysql -u root -p password 1、select * from Tab_Memo where name = ‘large’; // The result is Correct. 2、select * from Tab_Memo where name = ‘small’; //The result is Correct. Using unix socket mode ---------------------------------- 1、select * from Tab_Memo where name = ‘large’; // NO result return. 2、select * from Tab_Memo where name = ‘small’; //The result is Correct.
View Replies !
TimeStamps In Unix-Format
In my database i have to work with timestamps in unix-format like '20070101000000' for 2007-01-01 00:00:00. Now i have to look for the summary of the difference of two timestamps. The single difference of the timestamps are between one minute and about 15 hours. Now i don't know how to manage the correct sumary that should give me how many days, minutes and so on are summed.
View Replies !
Two Versions Of Mysql On Same Unix Box
I have mysql 3.23.58 version running on my unix box. I installed a new application on it which need mysql version > 4.0 so I thought of having two mysql versions running on the same unix box. I have first installation in /usr/local/mysql and I untared the new version in /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23. I am following instructions given in http://dev.mysql.com/doc/refman/4.1/en/multiple-servers.html I also created a empty file and named it as mysql.sock.new and stored it in /var/local. When I try to start this new version from /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23 using ./bin/mysqld_safe --datadir=/var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23/data & it says "A mysqld process already exists". Please tell me where I am going wrong and how to configure it properly?
View Replies !
Selecting By Unix Date
I'm trying to do a very simple thing: I have in my DB a row called at_date, in it I enter a 10 digits number of date, like: 1171869716. How can I select all the data from the table where the month is the current month (and year of course), or any month I choose? Better saving the dates in a different format?
View Replies !
GUI Interface For Unix Linux And Solaris Etc.
Is there a GUI Interface for MySQL ... that runs on Unix (Solaris, Linux, etc) There was an earlier thread about it - http://forums.devshed.com/showthread.php?t=51564&page=1&pp=15&highlight=GUI but most of these seem to run on Windows only.
View Replies !
Table Names On Windows/Unix
My development machine is Windows, while the production machine is Unix. I'll create a table on the Windows machine called "ABCD". I'll name it the same on the Unix machine. However, for some reason, the Windows mysql will rename it to "abcd", I assume because Windows is case insensitive. Of course, when I deploy the app to Unix, it crashes until I go through all my code and rename all database references and xml to "ABCD". Is there a way, on Windows, to make sure it calls the table "ABCD", so when I use my IDE's, it brings the tables in with the proper name?
View Replies !
UNIX TIME Conversion And Formating
I have a unixtime column. I was able to see the output like '1/22/2007' in one of my front end tool using FROM_UNIXTIME function. I want to apply a condition like Unixtime <'1/1/2007 and unixtime >'1/31/2007' or something like between. Some how i was unable to present that condintion because that format is not recognizing. can somebody help me to write a condition.. may be cast or convert or format..etc
View Replies !
Call Sql Script From Unix Prompt
Is there a simple command I can use to call a sql script externally from Unix? i.e. I have a script with several sql statements within it. Kind of like a delta file which holds thousands of update. I don't want to do a full import because only 1 of the columns needs to be updated.
View Replies !
Updating Database In WinXP From Unix
I'm currently volunteering my services for a non-profit organization and their current website is being hosted on Apache and on Unix. I'm totally new to PHP and MYSQL and learning as I go... my question is am I able to download the current database on the UNIX OS and make changes locally on WinXP and then deploy the changed database back to the remote server on UNIX? If so, are there special configs I must do?
View Replies !
Calling MySQL In Unix In One Line
I'm running MySQL in Unix and I need to run mysql without ever actually going into mysql. Basically I need to open the database, do what i need to do to the database and exit all in one line of code. If I have to, I could save the sql in a file and go mysql -h localhost -u ______ -p _________ << mysqlcode.txt or something with Unix redirection/piping. However, I'd like to do something more like mysql -h localhost -u _________ -p ________ - (use tempdatabase; select * from test;) Is there anyway to do this. The -e paramater looks like it might do that, but I can't figure out how to use it.
View Replies !
Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options. Here is what I did. But I dont see it working yet. 1. cd /var/db/mysql 2. chown mysql slowquery.log 3. touch /usr/local/etc/my.cnf 4. chown mysql /usr/local/etc/my.cnf vi my.cnf [mysqld_safe] -u mysql --log-slow-queries=/var/db/mysql/slowquery.log so now when I type: mysqladmin shutdown and than mysqld_safe & my sql restarts but the log files are not being used. also - how do I know if my my.cnf is being used at all?
View Replies !
|
TRACKER
