The timestamp tag has no wiki summary.
14
votes
1answer
11k views
How do I get the current unix timestamp from PostgreSQL?
Unix timestamp is the number of seconds since midnight UTC January 1, 1970.
How do I get the correct unix timestamp from PostgreSQL?
When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
5
votes
2answers
797 views
Single data type for imprecise date values, as allowed by ISO 8601
How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?
ISO 8601 allows date values with reduced precision. ‘1964’, ...
5
votes
3answers
117 views
“AT TIME ZONE” with zone name PostgreSQL bug?
I was answering this stackoverflow question and found strange result:
select * from pg_timezone_names where name = 'Europe/Berlin' ;
name | abbrev | utc_offset | is_dst
...
4
votes
2answers
524 views
Best way to handle dates prior to 1000 A.D. in MySQL?
I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000.
Is there a way that would be more convenient than either ...
4
votes
1answer
118 views
How can I improve this DB query? it takes too much time
The query is this:
$today = $date->getTimestamp();
SELECT t.*, u.screen_name, u.profile_image_url
FROM table AS t
LEFT JOIN users AS u
ON t.user_id=u.id_str
WHERE ...
4
votes
2answers
69 views
Alternative to sequence and timestamp: uniquely ordering records in time
Oracle 11gR2 Exadata
I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted ...
4
votes
2answers
1k views
Storing time with milliseconds in database
Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).
I was originally going to ...
4
votes
1answer
91 views
MySQL year 2038 vulnerable
I was studying Drupal and asked this question:
http://drupal.stackexchange.com/questions/45443/why-timestamp-format-was-chosen-for-users-created-field/45446#45446
I decided to check, how the CMS ...
4
votes
3answers
1k views
How can I time-stamp data in MongoDB?
I'm responsible for writing an app to juggle a huge amount of client and business intelligence information. This information involves records for various businesses and metrics like revenue, their web ...
3
votes
2answers
446 views
sql server management studio 2008 sucks up with memory yielding .net runtime failure
PROBLEM
sql server management studio 2008 sucks up with memory yielding a .net runtime error. It crashes with an empty message in an error-dialog but there can be found a little eventlog entry, ...
3
votes
1answer
148 views
Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp
I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following:
Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
2
votes
2answers
7k views
How to get the timestamp column in only milliseconds from PostgreSQL?
I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database.
If I select colums, it has a nice and readable format per default:
SELECT created FROM ...
2
votes
2answers
3k views
Comparing date and timestamp variables in a where clause in a procedure
I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
2
votes
3answers
1k views
Sorting by DATETIME slower than by TIMESTAMP?
I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ?
Because I noticed that ordering by numbered keys is done ...
2
votes
1answer
132 views
What happens when changing column type DATETIME to TIMESTAMP in MySQL?
I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines.
Is it possible? Is it slow? Will I loose the old information or MySQL can convert this ...
2
votes
1answer
2k views
Compare a date and a timestamp with time zone with now() in the same query?
I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
2
votes
1answer
296 views
MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP
In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
2
votes
3answers
510 views
How do i subtract a number of days from a prepared statement parameter in db2?
I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this:
SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
...
2
votes
3answers
365 views
Finding five-minute interval prior to current time
I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as ...
2
votes
0answers
262 views
Logic to convert a UTC date-time to a specific time zone
I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred.
I have a DIM ...
1
vote
2answers
53 views
Group Database Entries by time difference
I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
1
vote
2answers
677 views
LEFT JOIN conversion to INNER JOIN, can't change FROM clause
I have 2 tables:
BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation.
These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date.
...
1
vote
2answers
964 views
How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?
I want to set `addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed:
mysql> alter table mytable change c1 ...
1
vote
1answer
49 views
Deadline rule date input after now
Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date.
Currently I'm attempting this with a rule.
CREATE OR REPLACE RULE "DeadlineInput" AS
ON INSERT ...
1
vote
1answer
139 views
TimeZone parameter in postgresql.conf
postgresql documentation says:
All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the timezone
configuration parameter ...
1
vote
1answer
106 views
timestamp based concurrency
I am wondering if someone could help me out with a question I have about time stamp based concurrency.
I found the following practice problem on a website
It basically asks which of the following ...
1
vote
1answer
45 views
datetime vs timestamp in mysql 5.5
What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this?
I have a situation where I am trying ...
1
vote
1answer
44 views
Script to get duration
I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
0
votes
2answers
308 views
Dates changed to 0000-00-00
Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME ...
0
votes
3answers
1k views
Convert a Unix timestamp to a DATETIME in a View
I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out.
...
0
votes
2answers
3k views
MySQL Set UTC time as default timestamp
How do I set a timestamp column whose default value is the current UTC time?
MySQL uses UTC_TIMESTAMP() function for UTC timestamp:
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| ...
0
votes
2answers
77 views
Should I disable triggers, timestamps in a slave in mysql?
We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
0
votes
0answers
7 views
Fine grained (milliseconds) temporal indexes in neo4j
What are the best practices for modeling fine grained timelines in neo4j? If I were to use milliseconds as the grain in the pattern described here and here would it prove somehow problematic and ...
0
votes
0answers
36 views
is HyperTable timestamp sensitive to Y2038?
I've been investigating HyperTable to find out more about it's TimeStamp field, i'm still wondering at which future date will the timestamp field wrap around?
0
votes
0answers
66 views
database timestamp
What's the definition of timestamp?
The timestamp is just for a transaction? ( no matter what actions in the transaction. The timestamp is always the time when the transaction happened.)
OR the ...
0
votes
1answer
2k views
How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?
I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time).
As I understand it, the DATETIME is set to ...
0
votes
0answers
112 views
setting timestamp column as pk to prevent concurrent editing of records?
While I was familiarizing myself with all the new data types in MySQL, I noticed the TIMESTAMP data type, and thought "What if I make this TIMESTAMP field and the CustomerID field (the other pk) a ...
