The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
2answers
21 views

Execute query using daily timestamp (mysql event)

I was trying to find a way for give users a daily "point" per online day. So my plan was setup a MySQL event to run this query: UPDATE users SET points=points+1 WHERE timestamp > "X" (Where X ...
0
votes
1answer
74 views

localtimestamp or curent_timestamp for default value

In Oracle-12c I have some columns that I am converting from timestamp with time zone to timestamp with local time zone. Some of those same columns also currently have a default value of ...
1
vote
1answer
90 views

DB2 LUW Timestamp differences in versions

Im new to DB2, my back ground is in MS SQL server 2000 thru 2012. I have a developer using Local DB DB2 LUW v9.7.500.4299 FIXPACK_NUM = 5 She is selecting some data from a table where the field ...
1
vote
1answer
186 views

PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well ...
6
votes
2answers
124 views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
7
votes
3answers
2k 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 ...
0
votes
0answers
28 views

How does the current_timestamp() or now() work? I am getting inconsistent results

current_timestamp() returns time ahead of current time and then returns current time upon subsequent request. Below are two requests i gave to the mysql server in commandline. I was not able to ...
1
vote
1answer
53 views

Converting UTC time to timestamptz always adds the +01 offset in Postgres

In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone: SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC' this outputs correctly ...
3
votes
2answers
68 views

Generate a sequence of timestamps but skip given periods

I have a table things_to_do in PostgreSQL. For example: thing_id --------- 1 2 3 4 5 6 And I want to add a timestamp to every row: SELECT thing_id, '2016-02-10 19:57:00'::timestamp ...
1
vote
1answer
63 views

Filter created_at and count distinct minutes

I have this query to get records from a database: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp 'yesterday' AND created_at < timestamp 'today' ...
12
votes
2answers
45k 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 ...
14
votes
1answer
1k views

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH ...
1
vote
1answer
42 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do ...
1
vote
1answer
75 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
4
votes
1answer
137 views

Exchange rate table and conversion on the fly in query

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items. I'm working with Java and my first thought was that I would use some kind ...
3
votes
1answer
563 views

current_timestamp default column value working in Windows but not in Linux

This MySql query runs fine in Windows: CREATE TABLE `posts`.`table` ( `idTable` INT NOT NULL AUTO_INCREMENT, `Column1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`idTable`)); In ...
16
votes
2answers
41k 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
1answer
40 views

Select Unique Fields with Max Values in Each?

I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is. Addy (text), NW (number), UDate ...
2
votes
2answers
140 views

Automatically set timestamps for “create” and “modified”?

I have currently following SQL table: CREATE TABLE IF NOT EXISTS `xyz` ( `id` bigint(20) NOT NULL, `guid` char(36) NOT NULL, `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
2
votes
0answers
821 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 ...
0
votes
1answer
58 views

Filter column by records matching a timezone in Postgres

I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not ...
4
votes
4answers
34k 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. ...
1
vote
2answers
489 views

Default value for “microtime” column in MySQL

My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function. ...
0
votes
0answers
60 views

Syntax to change a DB2 timestamp from CST to EST

I am writing a code and the source are two DB2 databases. I see that both of them have a different timestamps. (one shows CST and another is EST). I need to make sure that both of them have similar ...
1
vote
1answer
120 views

Table replication with extra TIMESTAMP column on slave

I have a table on master and same table on slave with an extra column like InsertTime timestamp DEFAULT CURRENT_TIMESTAMP I want to keep record of time when there is update on slave. Replication ...
7
votes
1answer
7k views

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ...
0
votes
1answer
3k views

How to convert timestamp / rowversion to a date and time in SQL Server?

How to convert timestamp / rowversion to a date and time in SQL Server? I have several tables where I would like to track the last update, with column names like ModifiedAt, UpdatedAt, UpdatedDate. ...
5
votes
5answers
2k views

Using timestamp plus integer as primary key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
5
votes
2answers
2k views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 ...
3
votes
1answer
355 views

How to get the difference in days between 2 timestamps in PostgreSQL

I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not ...
3
votes
2answers
133 views

SQL server 2000 transactions with the same time

I have a BIG problem here. I'm creating a program that can be looping if the connection to the server is broken. But when the program is running for a few months, I found a bug on SQL server 2000. I ...
0
votes
1answer
848 views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
0
votes
1answer
42 views

Unix timestamp conversion

How can I output my Unix timestamps to human readable GMT? SELECT category ,fullname ,shortname ,idnumber ,startdate ,visible ,timecreated ...
7
votes
2answers
2k 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’, ...
0
votes
1answer
976 views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
1
vote
2answers
117 views

Mysql date_add returns wrong row in python, right row in phpmyadmin [closed]

I am trying to get the first and last record from a month. In phpmyadmin I ge the correct result. In python I don't. In fact python returns the same row for both of these queries. First number is ...
0
votes
0answers
60 views

compare time difference between current time with last data timestamp

I have a data server recording data every second. i need to setup a cron job that that runs a script that can give a reply if the now() is 24 hours more than the last data recorded. So that I can ...
1
vote
2answers
56 views

Organize repeating timestamped values in schema

I am creating models to hold some financial data. The information frequently is split into timestamped values, e.g: Some value, now | as of Jan 1st | as of Jun 1st | as of Sep 1st There are ...
1
vote
3answers
47 views

Last occurance of time relative to now()

What's the most elegant way to ask Postgres for the last 4:05pm relative to now()? I have been writing a fairly elaborate CASE with date_trunc's and my guess is it can be accomplished in a much ...
0
votes
3answers
321 views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
-4
votes
1answer
30 views

Get currentmilliseconds in postgres

I'm working on timestamp/datetime in my application. I'm using postgres db. What is the way to get current milliseconds (something like 1412706599000) in postgres?
1
vote
1answer
1k views

Time series and gap filling [closed]

I have to choose a database server for storing big time series data. Currently I use PostgreSQL, but I've found that Vertica supports automatic gap filling for missing data and that's really nice. ...
1
vote
2answers
174 views

fixed length table

I only have ever used mysql and storing data on .txt files. Currently I'm working with tick data (I have node.js on my server). My custom setup so far stores a fixed amount of tick data as comma ...
2
votes
2answers
25k views

How do I get the difference in minutes from 2 timestamp columns?

Can't seem to figure this out. Just need the difference in minutes between 2 oracle timestamp columns. END_TS START_TS ...
0
votes
1answer
130 views

Query MST instead of UTC for timestamps

on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates) ...
0
votes
2answers
311 views

Keep trailing zeros on Timestamp SELECT

This is the issue: I am reading rows with a timestamp column from a table and I need to make sure that the database returns the timestamp with a fixed width. But I am encountering instances where the ...
-3
votes
1answer
74 views

Timeline or time function in PostgreSQL

In MySQL I can use this select which show timeline view on data in my table/tables. SELECT COUNT(atribute), connection_timestamp FROM db GROUP BY DAYOFYEAR(connection_timestamp) ORDER BY ...
0
votes
1answer
70 views

What's the best database if i want save data temporally? [closed]

I want to create a datalog system so I need to store some int data temporally. What would be the best database (Relational, NO-SQL or some specific) for that purpose? And what would be the best way to ...
0
votes
1answer
123 views

Database timestamp

What's the definition of timestamp? The timestamp is just for a transaction? (no matter what actions are in the transaction. The timestamp is always the time when the transaction happened). OR the ...
0
votes
1answer
52 views

How can I always get Timestamp in certain format without having to explicitly define it everytime

When I run a select on a timestamp I get something like: 2015-01-02T23:22:36.000Z, 1/7/2015 11:03:31 AM In order to format properly I have to do: SELECT to_char(entered, 'MM/DD/YYYY HH12:MI:SS ...