Let $K$ be one of the following fields: the complex numbers, a finite field, a number field (and we could amalgamate the last two into the more general case of a field finitely generated overs its prime subfield).
In each case we can consider the category of smooth projective varieties over $K$, with morphisms being correspondences [added: modulo the relation of cohomological equivalence;
see David Speyer's comment below for an elaboration on this point]. (I am really thinking of the category of pure motives, but there is no particular need to invoke that word.)
In each case we also have a natural abelian (in fact Tannakian) category in play: in the complex case, the category of pure Hodge structures, and in the other cases, the category of $\ell$-adic representations of $G_K$ (the absolute Galois group of $K$) (for some prime $\ell$, prime to the characteristic of $K$ in the case when $K$ is a finite field).
Now taking cohomology gives a functor from the category of smooth projective varieties to this latter category (via Hodge theory in the complex case, and the theory of etale cohomology in the other cases). The Hodge conjecture (in the complex case) and the Tate conjecture (in the other cases) then says that this functor is fully faithful.
The consequence (if the conjecture is true) is that we can construct correspondences between varieties simply by making computations in some much more linear category.
Let me give an example of how this can be applied in number theory:
Fix two primes $p$ and $q$, and let $\mathcal O_D$ be a maximal order in the quaternion algebra $D$ over $\mathbb Q$ ramified at $p$ and $q$, and split everywhere else (including at infinity).
Let $\mathcal O_D^1$ denote the multiplicative group of norm one elements in $\mathcal O_D$.
Since $D \otimes_{\mathbb Q} \mathbb R \cong M_2(\mathbb R)$, we may regard $\mathcal O_D^1$ as a discrete subgroup of $SL_2(\mathbb R)$, and form the quotient $X := \mathcal O_D^1\backslash \mathcal H$ (where $\mathcal H$ is the upper half-plane).
We may also consider the usual congruence subgroup $\Gamma_0(pq)$ consisting of matrices in $SL_2(\mathbb Z)$ that are upper triangular modulo $pq$, and form $X_0(pq)$, the compacitifcation of $\Gamma_0(pq)\backslash \mathcal H$.
Now the theory of modular and automorphic forms and their associated Galois representations show that $X$ and $X_0(pq)$ are both naturally curves over $\mathbb Q$, and that there
is an embedding of Galois representations $H^1(X) \to H^1(X_0(pq))$. Thus the Tate conjecture predicts that there is a correspondence between $X$ and $X_0(pq)$ inducing
this embedding. Passing to Hodge structure, we would then find that the periods of holomorphic one-forms on $X$ should be among the periods of holomorphic one-forms on $X_0(pq)$.
Now the theory of $L$-functions shows that the periods of holomorphic one-forms on $X_0(pq)$ in certain cases compute special values of $L$-functions attached to modular forms on $\Gamma_0(pq)$. So putting this altogether, we would find that (given the Tate conjecture) we could compute special values of $L$-functions for certain modular forms by taking period integrals on the curve $X$. In certain respects $X$ is better behaved than $X_0(pq)$, and so this is an important technique in investigating the arithmetic of $L$-functions.
Now it happens that in this case the Tate conjectures is a theorem (of Faltings), and so
the above argument is actually correct and complete.
But there are infinitely many other analogous situations in the theory of Shimura varieties where the Tate conjecture is not yet known, but where one would like to know it.
There are also similar arguments where one uses the Hodge conjecture to move from information about periods to information about Galois representations and arithmetic.
I should also say that there are lots of partial results along the above lines in these cases; there are many inventive techniques that people have introduced for getting around the Hodge and Tate conjectures. (Deligne's use of absolute Hodge cycles, applications of theta corresondences by Shimura, Harris, Kudla, and others, ... .) But the Hodge and Tate
conjectures stand as fundamental guiding principles telling us what should be true, and which we would dearly like to see proved.
Summary: algebraic cycles are very rich objects, which straddle two worlds, the world of period integrals and the world of Galois representations. Thus, if the Hodge and Tate conjectures are true, we know that there are profound connections between those two worlds: we can pass information from one to the other through the medium of algebraic cycles. If we had these conjectures available, it would be an incredible enrichment of our understanding of these worlds; as it is, people expend a lot of effort to find ways to pass between the two worlds in the way the Hodge and Tate conjectures predict should be possible.
Another example, added following the request of the OP to provide an example just involving complex geometry: If $X$ is a K3 surface, then from
the Hodge structure on the primitive part of $H^2(X,\mathbb C)$, one can construct an
abelian variety, the so-called Kuga--Satake abelian variety associated to $X$. The
construction is made in terms of Hodge structures. One expects that in fact there should
be a link between $X$ and its associated Kuga--Satake abelian variety provided by some correspondence, but this is not known in general. It would be implied by the Hodge conjecture. (This constructions is discussed in this answer and in the accompanying comments.)
Best Answer
The rotation number in question has to do with the behaviour of a differential form $\omega$ on the manifold $X$ under rotation $e^{i\theta}$ of tangent vectors : a complex $k$-form $\omega$ has rotation number $p-q$ iff $$\omega(e^{i\theta}v_1,\dots,e^{i\theta}v_k)=e^{i(p-q)\theta}\omega(v_1,\dots,v_k)$$ (with $p+q=k$, of course).
This should explain the terminology, although it is not much in use today. Such a form is called a $(p,q)$-form, their space is denoted $\Omega^{p,q}(X)$, and the subspace of $H^{p+q}(X,\mathbb{C})$ (de Rham cohomology) of classes having a (closed) $(p,q)$-form representative is denoted $H^{p,q}(X)$.
Hodge theory then gives a decomposition $H^k(X,\mathbb{C})=\bigoplus_{p+q=k} H^{p,q}(X)$.
Now a topological cycle $C$ (or its class) of (real) codimension $2p$ has a Poincaré dual de Rham cohomology class $[\omega]$, for a closed $2p$ form $\omega$, such that intersection of cycles of dimension $2p$ with $C$ coincides with integration of $\omega$.
"The rotation number of $C$ is zero" means that one can choose $\omega$ of type $(p,p)$.
On the other hand, a cohomology class in $H^{2p}(X,\mathbb{Q})$ is said to be Hodge if under the Hodge decomposition $$H^{2p}(X,\mathbb{Q})\otimes\mathbb{C}\simeq \bigoplus_{i=-p}^{p} H^{p-i,p+i}(X)$$ it belongs to $H^{p,p}(X)$.
I hope this clarifies the equivalence of the two statements you found.